Is there any system stored procedure or any table that contains the creation date of
stored procedures in one database . I really want to know wich is the last stored procedure
created in a database.
Thanks.
I have created a database and I a table. Now I want to create a stored procedure.
I go to Programability=>Stored Procedures in my database and press right click=>New Stored Procedure. After I write the procedure I don't understand how to save it inside the datebase and execute it to preview the results in the Management Studio Express.
Hi
I need to create a view using a stored procedure .
The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.
I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .
Sujit
Hi,
I was wondering if there was a way to create two temp tables within the same stored procedure.
Can we have two create statements one after the other?
Thanks
I am trying to create a new stored procedure in Sql server managment studio express in a database.
I am getting an error message saying
Invalid object name 'Consumer_delete'.
Can you please tell why I am getting this error message?? Also , I need to make sure that the created procedure appears in the list of database objects after execution.
Thanks for your help
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE Consumer_delete
@ConsumerID int,
@BusinessId int
AS
BEGIN
DECLARE @intError int
DECLARE @ConsBusinessID int
SET NOCOUNT ON
SELECT @ConsBusinessID = CONSUMERBUSINESS.[ID]
FROM CONSUMERBUSINESS
WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId
DELETE FROM CONSUMERBUSINESS
WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId
DELETE FROM CUSTOMERREMINDER
WHERE ConsumerBusinessID = @ConsBusinessID
DELETE FROM NOTE
WHERE ConsumerBusinessID = @ConsBusinessID
DELETE FROM VISIT
WHERE ConsumerBusinessID = @ConsBusinessID
--ERROR HANDLING--------
SET @intError = @@ERROR
IF @intError <> 0
GOTO ExitError
RETURN 0
ExitError:
RETURN @intError
END
Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex() method reads the script file from starting to end and returns script text. My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime, @prmEndDate datetime,@prmEvent char(1)ASBEGIN SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) as EventDate from dbo.Parishioner P left outer join dbo.Sex S on P.SexId = S.SexId where (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) between @prmStartDate and @prmEndDate END But when i executing script file having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm
View 1 Replies View RelatedHi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies View RelatedThere is a stored procedure to run each Sunday. I would like to get the order date from previous Sunday through Saturday. Right now we code as
"OrderDate Between convert(datetime, '05/02/99') AND
convert(datetime, '05/08/99') "
Each week the dates have to be manually changed. How to use the date function to make it automated?
Hi all,I am trying to write a stored procedure that will delete records in atable that are older then 30 days. I am checking against a field thatis called PositionDate. Can someone point me in the right direction?TIA
View 4 Replies View RelatedHi,I have a parameter that is being input as a String via Crystal Reports.It's called 'School Year'and a typical value is '2006'. I need to be able to create a 'Date'that is compared to a field value in a where clause. In this case thedb field is called 'requisition_time_stamp' in the T_ORDER table. The'requistion_time_stamp' file is of type 'datetime'.Below is pseudo_code for what I need to do:T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)ANDT_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,1)The 'Date' method is a mock-up and is problematic because the 'SchoolYear' field needs to be converted to a number so it can be incrementedin the second statement, so I also need a conversion method, somethinglike 'ToNumber(School Year)' .I've looked at the Date functions in the Sybase documentation and Idon't see anything that fits what I'm trying to do.Any help would be greatly appreciated!
View 2 Replies View RelatedI am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this.
Select count(recordid),transactiondatefrom sometable group by transactiondate
Thanks
in advance
Hi there,
I am trying to ascertain how many users have registered with my site today. I am using the following stored procedure:
CREATE Procedure spGetUserCountToday
As
Return ( SELECT Count(*) FROM tblUserList WHERE role= "User" AND registerDate >= GETDATE()
)
GO
However the issue is the GetDate() function will only return those that have resgistered at the exact moment the query is run.
How can I change the GetDate to return only those users who have registered in??
Thanks in advance,
TCM
Hi, I ran into some problem here. The case scenerio is supposed to be like this:
- Each member can only make one appointment at any one time and only
make another appointment after the existing appointment expired.
- Each member is allowed to make an appointment at any time and must be at least 5 days in advance.
I managed to do the reservation for at least 5 days in advance but I
can't allow the member to make only one appointment. The member can
keep making appointments even though the existing appointment has not
expired. Can someone pls help? Thanks!
ALTER PROCEDURE spReserveAppt(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS(SELECT MemNRIC FROM DasMember WHERE MemNRIC = @MemNRIC)
RETURN -300
BEGIN
IF EXISTS
(SELECT COUNT(@MemNRIC)
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 5)
GROUP BY MemNRIC
HAVING COUNT(@MemNRIC) <> 0)
RETURN -301
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, @AppDate ,GETDATE()) > GETDATE()))
RETURN -302
END
END
INSERT INTO DasAppointment(AppDate, AppTime, MemNRIC) VALUES (@AppDate, @AppTime, @MemNRIC)
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spReserveAppt '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
Having a little trouble getting this one to work.
I have a stored procedure that selects items from the table...
SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews
I need to change it so it selects only the year needed. For instance, only items from 2004.
SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews WHERE fldDate = @myParameter
-Say myParameter = 2004 or whatever year is needed
The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure.
How can I be sure to only get a certain year?
Thanks,
Zath
Hello everybody
Help me to get the last processed date of a stored procedure.
I would like to know what stored procedures were updated recently. The only date I can see is the Creation Date. Any idea if the 'Last Change Date' is available?
View 6 Replies View RelatedHi,
I created this stored procedure to add 90 days to the current date, and return the new date:
CREATE PROCEDURE Get90Days
AS
SELECT DATEADD(day, 90, current_timestamp) AS "RETURNDAYS"
The procedure above returns this result:
2006-09-29 07:31:14.477
I need this procedure to return the date, only, like this:
09-29-2006
I tried using the convert switch to reformat the result to the date only, but have not been successful.
What does this stored procedure need to look like to return just the date, like this:
09-29-2006
I appreciate your help.
Thanks,
Bill
I have a stored date in my database, example 12 October 2000 which is a start date - now i would like to retrieve this data and the number of years and months from the current date (that is today - i am using the getdate() method)...how can i get the number of years and months passed? Thanks
View 3 Replies View RelatedThe following is part of my stored procedure where I could use some help:If I remark out the two lines regarding dates, it runs fine. The dateparameter is passed from a VB app as a text value in the format mm/dd/yy.Thanks for any help.CharlieSELECT * FROM dbo.TWQRHISTEARDOWN_HEADER where(WQR_TDH_MODELYEAR_T=@Model_Year OR @Model_Year IS NULL)AND (WQR_TDH_OFFLINEDT_Y>=@Start_Date OR @Start_Date IS NULL)AND (WQR_TDH_OFFLINEDT_Y<=@Stop_Date OR @Stop_Date IS NULL)
View 1 Replies View RelatedDear friends,
I have a stored procedure that returns some fiels. One of the fields is a datetime type.
The field return in the follow format : 2006-11-13 0:00:00
How can I return only 2006-11-13? How can I use the format function?
regards!!!
Hi:
Is there a way to find a column creation date? (or the latest date of type being updated)
I have tried information_schema.columns, syscolumns, sysobjects with no result related to.
thanks
David
Hi,
This is my first time posting in this forum. I am very green in DB2 admin so I would really appreciate your help.
The company had a data warehouse on DB2 7.1 on a sun solaries 8
I want to know how to retreive creation date from any table
Thanks
Does Microsoft SQL Server keep a record of an index creation date andlast modified date in a system table somewhere?
View 2 Replies View RelatedI have a script that is utilizing an FTP task to retrieve files from a Windows FTP site without any issues.
I need to check the dates of the files on the FTP site prior to downloading them as I only need files that have been modified. Since these files are hundreds of MB in size, downloading them first and them checking the date is not an optimal approach.
Can somebody please help me figure out how to check the creation dates of the files on the remote FTP directory?
Thanks!
on SQL Server 2000
They show only Create date
but I need know update date
because I install my system on customer's site and solve problem on customer site
and I can't bring all stored procedure back to my office and restore all stored
because of my database have two projects.
Please Help me.....
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
View 2 Replies View RelatedHi, i'm trying to calculate on the expiry date for product which include the start date and end date.
the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired.
the storedprocedure tat i've written is:
WHERE
DateDiff(day, StartDate, getDate())<=0
AND
DateDiff(day, EndDate, getDate())>=0
i wonder why it can's show the result!!! is the code correct?
Hi 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 need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mike
In order to pull back data from several servers to one central server I need to execute a stored procedure over each remote server. SQL 7 doesn't accept the following syntax when comparing date fields:
declare @cmd varchar(1000)
select @cmd = 'select * from ' + @server + '.DATABASE.dbo.TableName where
TableDateField =' + @variableDateField + '
exec (@cmd)
I've tried converting both datetime fields to varchar fields and I still get syntax errors. Any suggestions?
Can anyone help,
I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.
I am getting the following message:
Procedure 'TestReport' expects parameters '@StartDate', which was not supplied.
This happens on the objCom.Execute line.
This is my code:
Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report
Dim CRXDatabase As CRAXDRT.Database
Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")
Dim objCom As adodb.Command
Set objCom = New adodb.Command
Dim prm_one As adodb.Parameter
Dim prm_two As adodb.Parameter
Dim prm_three As adodb.Parameter
Dim fdate, tdate As Date
Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu")
Set CRXDatabase = CRXReport.Database
fdate = cboFrom.Value
tdate = cboTo.Value
With objCom
.CommandText = "TestReport"
.CommandType = adCmdStoredProc
.ActiveConnection = cn.ConnectionString
Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
.Parameters.Append prm_one
Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
.Parameters.Append prm_two
Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
.Parameters.Append prm_three
.Parameters.Refresh
End With
Set SQLRecs = objCom.Execute
CRXReport.DiscardSavedData
CRXDatabase.SetDataSource SQLRecs, 3, 1
This is my Stored Procedure:
CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS
SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1,
COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID
FROM dbo._Staff INNER JOIN
dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN
dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID
GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode,
dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID
Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate)
GO
Thanks in advance
Ian.
I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.
The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.
The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.
Here is a portion of the stored procedure:
alter proc uspSalesCommission
...
...
@IncludeOrderDateAsCriterion int,
@OrderDateRangeStart date,
@OrderDateRangeEnd date
as
BEGIN
...
...
WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
(@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)
In Excel VBA, the code passing the parameters are:
cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)
where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.
The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.