I Need To Find Out Info On How To Schedule A Stored Proc To Run In Sql 7 Every Few Hours?
Aug 24, 1999I have 2 stored proc's that i want to run an append query every 4 or 8 hours.
how does a person do this in ms sql 7?
ADVERTISEMENT
Global Stored Proc Find And Replace
Jan 29, 2004Hello all,
Being a relative newbie to large scale MSSQL development, I'd like to try and find out if there is some sort of utility, command or stored proc that I can use to globally change all text within my database's stored procedures.
For example... I would like to change a table name from dbo.xtable... to dbo.ytable... Is this possible?
Actually, I'd be happy with some way to search through all my stored procs to find a specific string (i.e. xtable in the example above).
Any help provided will be greatly appreciated!
Jordan Stradtman
I Can't Find My Stored Proc In Query Analyser
Jun 4, 20061/
I create stored procedure in Query Analyser using:
ALTER PROCEDURE dbo.unshippedtotal
@name char,
@ytd int output
AS
select * from vwaccount
select @ytd=sum(AccAccnnmb) from vwaccount
return
GO
But When I try to expand the list of stored procedures under the DB and the server name, I can t see my Stored Proc called "unshippedtotal"
Any ideas
2/ Another question pls:
Can we use Query Analyser to do the same tasks we do with Entreprise Manager like creating and modifying tables, Creating Stored Procedures, Modifying Views... (I m more familiar with Entreprise Manager but somebody told me it s better to use Query Analyser)
Thanks for coaching :)
In Stored Proc - How Do I Find The Next Key Value (integer) And Use It To Populate A Field In The New Record.
Nov 27, 2006Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies View RelatedFind Stored In Server Vide Proc For Given Table And Column
Jul 1, 2004Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi
Timestamp - How To Find Last 24 Hours
Jun 25, 2014I have a select statement which should do this here below:
WHERE TimeStamp = Last 24 hours'
how can I program it ? I have tried -1, getdate() and more with no success.
Schedule Stor Proc In 2005
May 21, 2007Hello
I KNEW this would cause some problems, but I just can't seem to find how to do it....
Basically I have a stored procedure in 2005 dbase (which updates the data), and I want to schedule it to run daily (at night) HOW CAN I DO THIS????
:mad: Y did they have 2 go and change it??? :eek:
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
How To Find Version Info.?
May 12, 1999How do I determine which version of SQL Server I have? In other words, I don't mean
6.5 or 7.0, rather, am I using the standard version, the desktop version, the enterprise, etc.?
Thanking you in advance.
Regards,
Michelle Turner
Where To Find Encryption Key Info?
May 29, 2008
Hi There
If a package is encrypted with user key, where can i find out what user key it is encrypted with ?
Thanx
MSSQL Remote Access: Can't Find Solution After 6 Hours Of Searching...
Jan 4, 2007Hello,
I'm trying to access the MSSQl 'remotey'. I open telnet and go to 127.0.0.1 1433 but can't. It seems I have done everything I can to fix it, but nothing solves the problem.
My firewall is disabled.
I have TCP/IP enabled in "SQL Server Configuration Manager" for both Client Protocol and protocols for MSSSQLSERVER (Shared Memory and NAmed Pipes are also both enabled). VIA is disabled.
In SQL Services, SQL Server Integration Services, Full Text Searc, SQL Server, SQL Server Analysis Services, and Server Browser are running. SQL Server Agent is disabled.
In Server Surface Area configuration, under Database Engine is running (automatic) and "Remote Connections" is enabled to TCP/IP and named pipes. Analysis Service has Local and Remote connections checked and is running (automatic). SQL Server agent is stopped.
Integration Services is running (Automatic) SQL Server Browser is running. (Automatic)
When I look in the error log, it shows...
Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message
Server is listening on [ 127.0.0.1 <ipv4> 1434].
Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message
Server is listening on [ 'any' <ipv4> 1433].
I have tried to telnet to port 1434 and 1433 but nothing happens. Does anyone know what else I need to be able to access that port??
Thanks
Foley
How To Find Service Pack Info
Jun 16, 2004how would i find out which service pack was applied to a specific sql 2000 server?
Thanks in advance!!!
Where To Find Info On The Concept Of SQL Tables
Feb 7, 2006i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?
View 20 Replies View RelatedWriting Expressions - Where Can I Find Info?
May 10, 2007Hi,
I realize that I need to learn how to write expressions for conditional splits, etc. Frankly, I don't even know what "language" expressions are written in. They look a lot like excel functions to me.
I tried searching the internet for some documentation, but it is sparse.
Can anyone point me to some links, etc that can teach me how to write my own expressions?
Thanks much
Where Can I Find Info About How To Backup Sql Server 2005 Using A .bat File?
Aug 30, 2006Where can I find info about how to backup Sql Server 2005 using a .bat file?
Is it possible doing it this way?
If so, can anyone direct me to some links how how to do this?
If not possible using a bat file... then how can I make schedueled backups? What tool to use and hopefully free.
Stored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedTransact SQL :: To Display Days Hours Mins Format Based On Business Hours
Apr 22, 2015I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
Calling A Stored Proc From Within Another Stored Proc
Feb 20, 2003I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Stored Proc Calls Another Stored Proc
Jan 13, 2006Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Calling Stored Proc B From Stored Proc A
Jan 20, 2004Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
Calling T SQL Stored Proc From CLR Stored Proc
Aug 30, 2007I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
How Can I Get The Sum Of Hours Stored In A Datetime Datatype
Feb 3, 2007I need to get (In a select statement, for a stored procedure) the SUM of hours stored in a datetime column (column name TaskHours). For example; (1/1/1900 3:30:00 AM, 1/1/1900 5:15:00 AM, 1/1/1900 4:45:00 AM) (3:30 + 5:15 + 4:45) this should sum to 13.5 hours.
(not 12.90)
I appreciate your help!
Breaking Down Total Hours Worked Into Day And Evening Hours
Sep 21, 2006I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View RelatedConverting Decimal Hours To Hours And Minutes
May 13, 2008I have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
How Do I Call A Stored Procedure That Takes 5+ Hours To Run From An Asp.net Page?
Jan 7, 2004Hello -
I need to call a SQL Server stored procedure, which takes over five hours to run, from an asp.NET web page. This procedure then calls a DTS package which is what takes 5+ hours to run. I need the user to be able to click on the 'run' button and have the page kick off the stored procedure (or the DTS Package if that will work instead) and display a message saying the load has begun and to check a 'status' link.
Right now I get a page timeout because it's waiting for results.
Any help would be greatly appreciated!
Thanks,
cat72
Adding Hours Between Each Time Block Stored In A Database?
Feb 26, 2013I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
Code:
Schedule for room called GR4
Course StartDate EndDate BeginTime EndTime HoursPerClass
MATH0001 06-FEB-13 19-FEB-13 0810 1700 9
MATH0002 20-FEB-13 04-Mar-13 0810 1700 9
MATH0003 10-JAN-13 05-Feb-13 0810 1700 9
MATH0004 22-APR-13 17-May-13 0810 1700 9
MATH0005 08-MAR-13 21-APR-13 0810 1700 9
MATH0006 07-JAN-13 09-JAN-13 0910 1300 4
MATH0007 20-JAN-13 17-MAY-13 1710 2000 3
MATH0008 08-JAN-13 18-JAN-13 1710 2000 3
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code:
select DISTINCT ssrmeet_room_code,
ssrmeet_start_date,
ssrmeet_end_date,
ssrmeet_crn,
ssrmeet_begin_time,
ssrmeet_end_time,
(((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
[code]....
Where Is Security Info Stored?
Apr 7, 2008How does SQL Server store Security Information. Accounts, Users, etc... Is there a particular table, like Master Table?
View 4 Replies View RelatedHow To Schedule One Stored Procedure
Mar 13, 2007Please,
How can I schedule one stored procedure using the SQL Server Management Studio Express, and Microsoft SQL Server 2005?
Thanks in advanced, best regards.
Eduardo F. de Melo
------------------
ASP.NET
SQL SERVER, MSDE, MYSQL
Schedule Stored Procedure
Jul 23, 2007Hi,
I have a simple stored procedure that gets the date and then deletes any records older than 90 days.
What is the best way to schedule it? I've got it working now just as a widows scheduled task which excecutes it using sqlcmd.
Is this the correct approach or is there a way to schedule it from within sql server?
Thanks
How To Schedule A Stored Procedure
Dec 22, 2005I'm new to sql server programing. Please help me to scedule a storedprocedure to run every hour. Thnak you all.
View 4 Replies View RelatedTrying To Add Info To Database Using A Stored Procedure
Jul 16, 2004Hi there :)
I was hoping someone could help me figure out why this doesn't work....
I have a class called ProductsDB with a method called AddProduct that looks like this:
public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);
SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);
SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);
SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);
SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);
SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);
SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);
SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);
SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);
SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;
return productId.ToString();
}
catch
{
return String.Empty;
}
}
And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:
// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);
And finally, here is my stored procedure:
CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS
INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)
SELECT
@ProductID = @@Identity
GO
Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.
Any ideas?
Many thanks indeed :)