I am using SQL Server Express within visual studio and am needing to create a stored procedure. The proble is that every time I finish and close it then it asks me if I want to save the changes which of course I want to.
But then it always comes up with a message box that says "invalid object name" followed with the name of the stored procedure.
??? why? I try creating a stored procedure from scratch or copying the code of another stored procedure. It alway gives me that message and does not let me save the stored procedure. I tried changing the default name of "dbo.storedprocedure1" or even leaving it as is and always I get the message that says "invalid object name: dbo.storedprocedure1" and it does not let me save the procedure.
Hello Everyone,I am sure, for the knowing, that this is a simple question.I have a table called tblTickets and a table called tblProjects. I would like to make a stored procedure that does the followingCopy entire contents of tblTickets record to tblProjects record and also changes tblTickets.sDetails to contain some text like "This ticket has been escalated into a project click here to view the project"tblProjects.sDetails would remain the same as the original.tblProjects will have additional fields but they can remain blank for the time being.ThanksMatt
What I am doing:I am in the process of creating a "log" that records when I update a record. I would like a new row to be inserted into BookLog each time a book's information is updated. Below I have the "update" code and below that is the code that is suppose to be inserting the newely updated record in the BookLog table. **What the problem is**: I've tested the insert code, everything works EXCEPT BookEntryDate. I do not want to update the BookEntryDate when I am inserting it into BookLog. I just want to keep the original entry date from the Books table thats already there. In the BooksLog table I have "BookEntryDate" to not allow nulls...when i go to test i get an error that says "Procedure 'BookUpdate' expects parameter '@BookEntryDate', which was not supplied." I've tried several things and I am stuck :-/ I need some ideas. Thanks for your help!Alter Procedure dbo.BookUpdate@BookEntryDate datetime,@BookSummary nvarchar(1000),@BookComment nvarchar(50),@UserID nvarchar(50),@BookID bigint AsDeclare @Now datetimeSet @Now = GetDate()Update BooksSet Books.BookSummary = @BookSummary,Books.UserID = @UserID,Books.BookComment = @BookCommentWhere Books.BookID = @BookIDINSERT INTO BookLog(BookID,BookEntryDate,BookSummary, UserID,BookCommentLogDate)VALUES (@BookID,@BookEntryDate, @BookSummary,@UserID, @BookComment,@Now)RETURN GO
hi, i have a sqlserver2005 table 'member' with the fields 'id','name','datecreated','datemodified' and 'memberlevel' (which the default value is 1). Now i want to create a stored procedure that will run automatically once a day, this procedure will reset to 0 the 'memberlevel' that are 1 if the 'datemodified' is greater than 3 months. can anyone help me on this, thanks!
When I verify syntax, I get this error: Msg 1038, Level 15, State 4, Procedure webservices_BENEFICIAL_USES_DM_SELECT, Line 8 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Here's the proc: CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT -- Add the parameters for the stored procedure here @DISPOSAL_AREA_NAME varchar(40) = "" AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT* FROMBENEFICIAL_USES_DM END GO
Hey all, Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation] @Id int, @Name varchar AS DELETE FROM Classifieds_Terminals WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this: USE [CLASSIFIEDSDB2.MDF] GO DECLARE @return_value int EXEC @return_value = [dbo].[RemoveLocation] @Id = 18, @Name = N'Terminal A' SELECT 'Return Value' = @return_value GO
It returns 0 and nothing happens....???
Here is the table: id | Name 18 Terminal A18 Terminal B18 Terminal C
Hi, I got a problem of a sp. Did the sp can use over one GO script? It is because after I create the below scripts in the sp and checked no syntax error and then apply changed. I re-open this sp, it just save my scripts upto the first GO statement . The scripts from "IF EXISTS..." has not saved. But I try to run the script in analyzer is successful. How can I fix it in Enterprise manager? Thanks. Here is my test code: CREATE PROCEDURE sp_Test AS--set nocount onSELECT id, name from MainGO--insert the record in SimpleReports tableIF EXISTS (select ID from Main where id=5)delete from Main where id=5GOINSERT INTO Main ( ID, Name, Quantity)VALUES ( '6', 'hij', '60')GO
I have what I hope is a simple question about a stored procedure. If this is not the right spot for this, I offer my apologies. I am planning on calling a stored procedure from my ASP.Net site using C#. What I want to do is pass a variable to the procedure and using that varible, put together another variable that I will pass back. I am very new to all but the most basic stored procedures so I was not shocked when my code didnt work. Can anyone toss me a hint? I am actively working on this and if I find anything on a website to assist and have a breakthru, I will be sure to update you all. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON goCREATE PROCEDURE [dbo].[GetAttySectyData] (@ID as int) AS declare @First as varchar(200) declare @Middle as varchar(50) declare @Last as varchar(200) declare @Name as varchar(255)@First = SELECT value FROM dbo.text WHERE efield_id = 10741 AND employee_id = @ID @Middle = SELECT value FROM dbo.text WHERE efield_id = 10906 AND employee_id = @ID @Last = SELECT value FROM dbo.text WHERE efield_id =10740 AND employee_id = @ID Select @First+' '+@Middle+' '+@Last from text AS @Name RETURN
I am trying to figure out the syntax for an SQL statement I have in a stored procedure.CREATE PROCEDURE dbo.forum_post_add @forumId integer, @fpTopic char(100), @fpBody text, @fpPoster integer AS Insert into forumPost (forumId, fpTopic, fpBody, fpPoster) values (@forumId, @fpTopic, @fpBody, @fpPoster) UPDATE forum SET forumThreads = forumThreads + 1 where forumId = @forumId UPDATE forumGroup SET fgThreads = fgThreads + 1 where fgID = select forumGroup from forum where forumId = @forumId GOThe statement in question is the second UPDATE. How do I create an UPDATE statement that selects that correct record based on the single result of a SELECT statement?
I am also not too sure about the 'fgThreads = fgThreads + 1' thing, is this the best way to increment a value?
I know I should also be using transactions and probably a few other bells and whistles but I'll worry about that later.
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0. If I set it to 0 which is disabled, the user can still login. Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
If I put 5 or 6 stored procedures within another stored procedure will they be executed sequentially or all at once? Is there any method. I can manage this process with some code I was just wondering how query analyzer or an sp would handle this by default.
Hi All,I am pretty new to stored procedure and I have a quick question. Iappreciate any help offered.I am writing a stored procedure as follow. This is just an example:select field1, field2 from table1 where field1 = 1I need to change this stored procedure so that if field2=0 then it willbe displayed on the web application as 'Yes', if field2=1 then I needto display the text 'No' on the web application.Field2's type is bit, ie: its' value is either 1 or 0.Thanks for any suggestion.Sincerely,Teresa
room table looks like roomID room name 1 prikey 100 2 101 3 102
Color table looks like
colorID Color name 1prikey red 2 blue 3 grey 4 white
I will need to pull the data from the system table based building name, room name and color. If the parameter is zero I will not filter on that parameter so will only be filtering on one parameter for each time the procedure is called. Any ideas on a simple query, thanks!
create procedure [dbo].[usp_procedure]@var1 int=null output,@var2 varchar(25)=null output,@var3 varchar(25)=null output,@var4 varchar(25)=null output,@var5 varchar(25)=null output,@action char(1)=null outputasbegin set nocoutn on;if @action = 'S'begin select * from t_table order by @var1end
in my script I call to this stored procedure but, this send me a error. my script is $action="S";// Set up T-SQL query.$tsql = "call dbo.usp_T_Usuarios (@action=?)";// Assign parameter values.$params = array($action);// Specify types for parameters.$stmt = sqlsrv_query( $conn, $tsql, $params);echo "<br><br>Executed.....<br><br>";// Create and execute the statement. Display any errors that occur.if(!$stmt){ echo "Error executing the sp...<br>"; die( print_r( sqlsrv_errors(), true));}else{ echo "<br>Execute sp....<br><br>"; echo $stmt;} and the error is:
conected...The conection was succefully.Executed.....Error executing the sp...Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido [message] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido [message] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido ) )I need help me. some body tell me what is my error. please.
I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.
I suspect I have a but in a stored procedure that is used to delete records in a table. Here's the code for the gridview and its SQLDataSource 1 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 2 DataKeyNames="table2_id" DataSourceID="SqlDataSource1"> 3 <Columns> 4 <asp:CommandField ShowDeleteButton="True" ShowSelectButton="True" /> 5 <asp:BoundField DataField="table2_id" HeaderText="table2_id" 6 InsertVisible="False" ReadOnly="True" SortExpression="table2_id" /> 7 <asp:BoundField DataField="table1_id" HeaderText="table1_id" 8 SortExpression="table1_id" /> 9 <asp:BoundField DataField="name_2" HeaderText="name_2" 10 SortExpression="name_2" /> 11 <asp:CheckBoxField DataField="yesno" HeaderText="yesno" 12 SortExpression="yesno" /> 13 <asp:BoundField DataField="MyArtificialColumn" HeaderText="MyArtificialColumn" 14 ReadOnly="True" SortExpression="MyArtificialColumn" /> 15 </Columns> 16 </asp:GridView> 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 18 ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" 19 DeleteCommand="EraseIt" DeleteCommandType="StoredProcedure" 20 SelectCommand="StoredProcedure5" SelectCommandType="StoredProcedure"> 21 </asp:SqlDataSource> Recently, "Wreck of u" kindly provided me with the stored procedure "StoredProcedure5" (line 20) which populates the gridview as shown. What I would like to now is write a stored procedure that DELETES a row. As like 19 shows, I attempted to write such an SP called EraseIt. However, when I click on Delete in the browser for a row, I get the error message "Procedure EraseIt has no parameters and arguments were supplied. The following is the code for EraseIt. Do I in fact have an error with specifying the input parameter "@table2_id", and or am I doing something else wrong? 1 ALTER PROCEDURE [dbo].[EraseIt] 2 AS 3 DECLARE @TABLE2_ID int 4 BEGIN 5 DELETE FROM DBO.TABLE2 WHERE TABLE2_ID = @TABLE2_ID 6 END 7 Thanks!-Kurt
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?Thank you
ALTER PROCEDURE companyinsert@companyid INT IDENTITY(1,1) NOT NULL,@companyname varchar(20),@address1 varchar(30)ASINSERT INTO companymaster ( companyname, address1)VALUES (@companyname,@address1)i don't want the companyname having the same names are recorded again with the different company id..Can anyone help me and modify my code according it's giving error...in the @companyid.It is being done in sql server 2005 with asp.net C# 2005
I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control.. I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..create table dbo.custom_Profile ( UserId uniqueidentifier not null Primary Key, IamWeAre nvarchar(50) null, InterestedIn nvarchar(256) null, IntroTitle nvarchar(100) null, TellOthers nvarchar(MAX)null, MaritalStatus nvarchar(20) null, BodyType nvarchar(50) null, Race nvarchar(20) null, Smoking nvarchar(20) null, Drinking nvarchar(20) null, Drugs nvarchar(20) null, Education nvarchar(256) null)
I have a table (displayed in a gridview) of services we provide. I have another table (the logfile) that displays the current status of those services. This GridView displays the service and current status.When a new service is created there is obviously no status yet. This causes a problem because my stored procedure (below) does not display that new service in my GridView. How can I ensure EVERY service is included in my dataset regardless of whether or there is a status? (and how can I get away from having to group by all the time?) select s.servicename, s.opr, c.commentid,c.comment, c.etic, t.statusfrom svc_service sinner join svc_comment c on c.serviceid = s.serviceidinner join svc_status t on t.statusid = c.statusidgroup by s.servicename, s.opr, c.commentid, c.comment, c.etic, t.status TIA, Jon
I have an inline sql query which i want to convert to a stored procedure. The query is simplified as follows:
string sql = "SELECT * FROM property_property WHERE Location = " + location; if(state != null) sql += " AND State = " + state; if(cost !=null) sql += " AND Cost = " + cost;
The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc
I know some of the basics and managed to convert every other query except this.
I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value. Here's my simple stored procedure to show one record: CREATE PROCEDURE DisplayCity(@CityID int)AS SELECT City From City where CityID = @CityIDGO My code for displaying the City: Sub ShowCity() Dim strConnect As String Dim objConnect As SqlConnection Dim objCommand As New SqlCommand Dim strCityID As String Dim City As String
'Get connection string from Web.Config strConnect = ConfigurationSettings.AppSettings("ConnectionString") objConnect = New SqlConnection(strConnect)
objConnect.Open()
'Get incoming City ID strCityID = request.params("CityID")
'Display SubCategory City = "" & objcommand.ExecuteScalar().ToString() lblCity.Text = City
lblChosenCity.Text = City
objConnect.Close()
End Sub
Here's the code I'd like to get help with changing into a stored procedure: Sub BindDataList()
Dim strConnect As String Dim objConnect As New System.Data.SqlClient.SQLConnection Dim objCommand As New System.Data.SqlClient.SQLCommand Dim strSQL As String Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter() Dim dtsAdvertiser As New DataSet() Dim strCatID As String Dim strCityID As String Dim SubCategory As String Dim SubCategoryID As String Dim BusinessName As String Dim City As String
'Get connection string from Web.Config strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New System.Data.SqlClient.SQLConnection(strConnect)
'Create a new DataAdapter object dtaAdvertiser.SelectCommand = objCommand
'Get the data from the database and 'put it into a DataTable object named dttAdvertiser in the DataSet object dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")
'If no records were found in the category, 'display that message and don't bind the DataGrid if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then
lblNoItemsFound.Visible = True lblNoItemsFound.Text = "Sorry, no listings were found!"
else
'Set the DataSource property of the DataGrid dtlAdvertiser.DataSource = dtsAdvertiser
'Set module level variable for page title display BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName") SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory") SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID") City = dtsAdvertiser.Tables(0).Rows(0).Item("City")
'Bind all the controls on the page dtlAdvertiser.DataBind()
end if
objCommand.ExecuteNonQuery()
'this is the way to close commands objCommand.Connection.Close()
I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report and I have never done a stored procedure and I was wondering if it would be ease to do?
FirstName..LastName.....StartedDate Randy......Simpson......5/4/2007 10:00:00 PM Steve......Rowe.........5/2/2007 10:00:00 PM Eric.......Dickerson....5/4/2007 10:00:00 PM Gloria.....Sanches......5/1/2007 12:00:29 AM Andres.....Marcelino....5/1/2007 12:06:31 AM katie......ryan.........6/4/2007 12:08:35 AM Denise.....River........6/4/2007 12:27:14 AM Kellog.....Stover.......6/5/2007 12:37:20 AM Glenn......Sanders......6/1/2007 12:42:40 AM
Im trying to create a stored procedure that selects everything from afunction name that im passing in through a parameter..create procedure SP_selectall(@functionname varchar(25))asselect * from @functionamegoI keep getting this error:Server: Msg 137, Level 15, State 2, Procedure SP_selectall, Line 5Must declare the variable '@functioname'.Whats the issue?
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple. I have used sql server 2000 and i am new in sql server 2005.
Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert 2 3 @companyid int, 4 @companyname varchar(20), 5 @address1 varchar(30) 6 7 AS 8 9 INSERT INTO companymaster 10 ( companyname, address1) 11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which was not supplied.
The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....
I am trying to create stored procedure i Query analyzer in visual studio 2005. I am havingdifficulty though. Whenever I press the execute button, here is the error message I get: Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21Incorrect syntax near 'MarketName'. Here is the stored procedure. Note that the very first column in named "MarketId" but I did notinclude it in the stored procedure since it should be auto generated. USE [StockWatch]GO/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2), @MarketDescription nvarchar(100)) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate MarketDescription)VALUES( @MarketCode @MarketName @LastUpdateUser @MarketDescription)
I was trying to create a new stored procedure. It's pretty straight forward using Insert Into. Here's what I wrote: CREATE PROCEDURE dbo.InsertPicks@ID varchar,@Race int,@P1 varchar,@P2 varchar,@P3 varchar,@P4 varchar,@P5 varchar,@P6 varchar,@P7 varchar,@P8 varchar,@P9 varchar,@P10 varchar, INSERT INTO tblPicks(pRace, pPlayer, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)VALUES(@Race,@ID,@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10) RETURN I first wrote the T-SQL code myself, but when I opened the Query Builder so I could validate it and check it. It validated and when I executed it, it worked great and inserted a new record into the table. Yet, when I closed the Query Builder, and tried to save the Stored Procedure (which I assume is how you create it) I get an error that there is a Incorrect Syntax near "Insert". I see nothing wrong with the syntax, and it worked fine in the Query Builder. What am I doing wrong? Thanks, Bob
Is there a way I can use one SQL server 2005 (full edition not express) Stored procedure that can execute a .SQL file as if it were in the SQL management studio Query window? I have the situation where I have some .SQL file which were created with the 'Create to File' option in SQL Management studio. I then want to send them to a user who can execute them to create a SP on his SQL server without having to run the SQL management tools. Regards Clive
--Drop procedures if they exsit if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItems]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PagedItems] GO
if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItemsByTime]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PagedItemsByTime] GO
if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_selectedEventMessage]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) drop procedure [dbo].[sp_selectedEventMessage] GO
--Definitions of procedures USE LanDeskDB GO
CREATE PROCEDURE sp_PagedItems ( @QueryVARCHAR(1000), @Pageint, @RecsPerPageint, @startDateVARCHAR(100), @endDateVARCHAR(100), @allTimeint, @flagint ) AS BEGIN SET NOCOUNT ON DECLARE @SQL VARCHAR(2000) DECLARE @Order VARCHAR(200) DECLARE @TotalBIGINT
CREATE TABLE #TempTable ( TempTableID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1), EventLogID BIGINT , EventDateTime datetime, MachineID INT , TypeID INT , SessionID INT , SourceID INT , MessageID BIGINT , UserID INT, CategoryNumber INT, EventID INT ) IF (@flag = 1) BEGIN SET @Order = 'ORDER BY EventDateTime' END IF (@flag = 2) BEGIN SET @Order = 'ORDER BY SessionID,EventDateTime' END IF (@flag = 3) BEGIN SET @Order = 'ORDER BY TypeID,EventDateTime' END IF (@flag = 4) BEGIN SET @Order = 'ORDER BY CategoryNumber,EventDateTime' END IF(@allTime <> 1) BEGIN IF(LEN(@Query)>1) BEGIN SET @Query = @Query+'AND EventDateTime>='''+@startDate+''' AND EventDateTime <= '''+@endDate+'''' END ELSE BEGIN SET @Query = 'WHERE EventDateTime>='''+@startDate+''' AND EventDateTime <= '''+@endDate+'''' END END SET @SQL= 'INSERT INTO #TempTable (EventLogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID)'+ 'SELECT EventlogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID FROM EventLog '+ @Query+' '+@Order EXEC (@SQL)
CREATE TABLE #TempTableTwo ( TempTableTwoID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1), TempTableID BIGINT, EventLogID BIGINT , EventDateTime datetime, MachineID INT , TypeID INT , SessionID INT , SourceID INT , MessageID BIGINT , UserID INT, CategoryNumber INT, EventID INT, )
SELECT * FROM #TempTable T WHERE T.TempTableID >@FirstRec AND T.TempTableID < @LastRec
SELECT TempT.EventLogID AS EventLogID,TempT.EventDateTime AS EventDateTime, Ma.MachineName AS MachineName,Se.SessionName AS SessionName,Ty.TypeName AS TypeName, TempT.CategoryNumber AS CategoryNumber,Us.UserName AS UserName,So.SourceName AS SourceName, TempT.EventID AS EventID FROM #TempTableTwo TempT,Machines Ma,Types Ty,Sessions Se,Sources So,Users Us WHERE TempT.MachineID = Ma.MachineID AND TempT.TypeID = Ty.TypeID AND TempT.SessionID = Se.SessionID AND TempT.SourceID = So.SourceID AND TempT.UserID = Us.UserID
SELECT COUNT(*) FROM #TempTable
DROP TABLE #TempTable DROP TABLE #TempTableTwo SET NOCOUNT OFF END GO
CREATE PROCEDURE sp_PagedItemsByTime ( @QueryVARCHAR(1000), @Page int, @RecsPerPage int, @startDateVARCHAR(100), @endDateVARCHAR(100), @allTime int ) AS BEGIN EXEC sp_PagedItems @Query,@Page,@RecsPerPage,@startDate,@endDate,@allTime,1 END GO
CREATE PROCEDURE sp_selectedEventMessage ( @EventLogID int ) AS
BEGIN
SELECT Ma.MachineName,Ev.EventDateTime,Se.SessionName,Ty.TypeName,So.SourceName,Me.MessageDescription FROM EventLog Ev,Sessions Se,Types Ty,Sources So,Messages Me,Machines Ma WHERE Ev.EventLogID = @EventLogID AND Ev.SessionID = Se.SessionID AND Ma.MachineID = Ev.MachineID AND Ev.TypeID = Ty.TypeID AND Ev.SourceID = So.SourceID AND Ev.MessageID = Me.MessageID
END
GO
I got the error messge as Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItems, Line 107 There is already an object named 'sp_PagedItems' in the database. Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItemsByTime, Line 13 There is already an object named 'sp_PagedItemsByTime' in the database. Server: Msg 2714, Level 16, State 5, Procedure sp_selectedEventMessage, Line 12 There is already an object named 'sp_selectedEventMessage' in the database.
But I already delete those procedures before I create them. Could anyone give some suggestion?
Can I create system stored procedure in sql 7.0? After I used 'alter' to modify a system sp, it's category change from 'system' to 'user'. Is there way to change it back? Thanks a lot!
