I am moving from Access to SQL and I am trying to create DELETE and a
SELECT procedures.
I was following the Asp.Net 2.0 Membership, Roles, etc procedures but I
am not getting there.
Could someone, please, post just the 3 procedures for this example?
It would be a start for me to work on ...
I have related two tables: <Content> and <ContentLocalized>
1. SELECT [ContentHtml] Given [ContentName] and [ContentCulture]
2. DELETE a content in <Content> and all its dependencies in
<ContentLocalized>
given [ContentName]
3. DELETE a content record in <ContentLocalized> given [ContentName]
and [ContentCulture]
I allready have an Access Query for (1) but I don't think I should try
to use it in SQL:
SELECT ContentLocalized.ContentHtml
FROM Content INNER JOIN ContentLocalized ON
Content.ContentId=ContentLocalized.ContentId
WHERE (((ContentLocalized.ContentCulture)=[@ContentCulture]) AND
((Content.ContentName)=[@ContentName]));
Thank You Very Much,
Miguel
I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out
REATE PROCEDURE InsertTerms AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName], [Pictures])
SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS FROM EmployeeGamingLicense AS a WHERE a.STATUS = 'TERMINATED' IF @@Error <> '0' RETURN
In SQL server 2000 enterprise manager you were able to select multiple stored procedures and drop them all in one go. In 2005 all the stored procs are listed differently (in a tree view) and you cannot therefore select multiple SP for deleteing. Is there another way to accomplish this?
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not. CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO <asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button> Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub ____________________________________________________________ CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO <asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button> Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
I have a script that delete records daily and shrinks the log file. I tested it with the SQL Analyzer and I should get the same results? I put this in a job that runs at night and when checking today when I got in the jobs ran successfully but the datafile continued to grow along with the transaction log. Here is the script: Use wslogdb62 go delete * FROM dbo.INCOMING_temp
WHERE DATE_TIME <=dateadd(day, -180, getdate()) DBCC SHRINKFILE(wslogdb62_log, 1) as you know I get a error from the analyzer.
I've constructed the SELECT statement to show the rows I want - and it shows 189 rows. Now I want to delete these rows. Here is the SELECT statement:
SELECT tblinqty.* FROM tblinqty LEFT JOIN tblmporder ON tblinqty.linkidsub = tblmporder.orderno WHERE tblmporder.orderno IS NULL and tblinqty.transtype = '0' and tblinqty.linkid = 'MP'
If I change the statement to "select * from tblinqty where exists ()", putting the above command inside the (), it returns over 12,000 rows! My intention is to change the SELECT into a DELETE by replacing the "select *" with a "DELETE" - but if I do that it will delete the wrong rows. How is the easiest way to turn the above successful SELECT statement, which yields 189 rows, into a DELETE statement which also deletes the same 189 rows?
I've tried changing the statement to a WHERE, thinking it would be easier to change to a DELETE, but the following yields 0 rows:
SELECT tblinqty.* FROM tblinqty WHERE tblinqty.linkidsub = tblmporder.orderno AND tblmporder.orderno IS NULL and tblinqty.transtype = '0' and tblinqty.linkid = 'MP'
We use stored procedures to populate the properties of data layer objects. Is there any advantage or disadvantage (performance wise) of using "SELECT * FROM Table1" Vs
SELECT COLUMN1, COLUMN2, COLUMN3, ETC FROM Table1.
Using individual column names in the select store procedure is getting difficult to maintain for large number of columns or when columns are added or removed.
Ok, I currently have a query which is basically doing the following:
Code:
SELECT ... WHERE x IN (101,102...)
I have a couple of questions:
Firstly would making this to a stored procedure really make much of a difference to performance considering the WHERE IN clause.
Secondly how can I pass this list of values to use in the WHERE IN clause to the stored procedure. I tried nVarChar but really wasn't surprised when it didn't work... and can't really think of another clear way.
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc. How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4. Maybe I didn't explain it good enough for you, please tell me then!! Thanks!!
when i try to get data or insert new from some table. SQL show this message error --------------------------------------------------------------------------- Server: Msg 3624, Level 20, State 1, Line 1
Location: q:SPHINXNTDBMSstorengdrsinclude ecord.inl:1447 Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW SPID: 52 Process ID: 2116 Connection Broken ------------------------------------------------- how to fix this problem ? These table have around 2,900,000 records.
I am trying to do the following for a few tables in my DB. I have an ODBC datasource and would simply like to update my DB from this datasource. I want to pull the table data from my ODBC datasource, delete any existing data in my SQL tables and then insert the data from my ODBC datasource into SQL. I am trying to create an SSIS package to do this but am not sure where to start. Any help or examples would be appreciated.
I need to keep track of the number of hits on a particular page. Im using a stored Procedure
What I want to do is get the number of hits and increment it by one :)
ie: Sub Procedure should be like below
SELECT noOfHits WHERE pageName = 'bla bla'
noOfHits = noOfHits + 1 etc.
Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'
What I need to do in essence is:
1. Check that a particular row exists. if it doesnt create it. 2. Increment a value (by one) to a column in this particular row.
Phew. Hope you got that. Any ideas much appreciated,
i want use stored procedures in select query. for example : select * from table where (... run a stored procedure ....) or update from table set a=@a where (... run a stored procedure ....)
hi i have form view that retrieves a single row i dont want to use SQLDataSource default Select/Update/Insert/Delete buttons i am using Stored Procedures I want to have my own buttons, the select has parameters, how to retrieve data for that parameter?
Can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int while (select count(*) from @MyTempTable) > 0 begin select top 1 @FieldVal = FieldVal from @MyTempTable -- process @FieldVal then delete the row delete top 1 from @MyTempTable end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to delete the selected data from a table colum from a select statement.
This is the select statement
SELECT RFC822 FROM SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification & invitation%')
What the delete statement would look like. I've tried replacing Select with delete from but get a sytax error.
Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot!
Hi there, I have a database table with a field to specify the username of records that particular user is allowed to access (read only). I want to be certain the user does not have access to any other records. So I'm using a stored procedure to query the table with "WHERE dbuser = USER". My problem is that the stored procedure is returning an error that the table I'm querying cannot be found, and I suspect it is because the user does not have proper permissions to see those tables, even through the stored procedure. So my question is, how do I grant the user sufficient access for this stored procedure to work without outright granting them select permission on the tables? Much appreciated!
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
[Code] ....
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
Greetings. I have been trying to develop an SSIS package that updates external data (Visual FoxPro tables) from SQL Server 2005. I have tried this various ways: using various Data Flow task components that flow to an OLEB Destination; using an Execute T-SQL Task; and even trying Management Studio interactively with the OpenDataSource('vfpoledb', etc.) statement. For each of these techniques, I have no problem performing a SELECT from the VFP data. Also, I have no problems performing an INSERT of new records using any of these techniques. However, both UPDATE and DELETE of existing records fail.
Is it possible the the OLE DB driver doesn't support UPDATE and DELETE operations? It appears that I'm not allowed to change or delete existing records, only add new ones. Or, are there other techniques I can be trying?
I am aware that updating FoxPro data can be performed by pulling the data from SQL Server into FoxPro. For our purposes, it would be more convenient if the processes could be initiated and managed from the SQL Server side of things instead.
In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable (like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns. (I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
Hi All, As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery) that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery ( @select_query nvarchar(500) ) as begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
Hi,Has any one ever compared the performance of calling a DataTable'sSelect method with a stored procedure doing the same thing?My point is:dataRows = DataTable.Select(filter) is better orPassing paramters to stored procedure?The datatable holds about 500-700 rows at any given time.If I select one of the approaches the business logic will go intorespective layers.With dotnet in picture what would be a good approach- Have the data in Datatable and do a filter on the data or callstored procedures which has been the convention.Can some one pl. suggest?
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
