I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .
Now there are some entries in the subscription list at the subscriber which need to be deleted .
The list shows the name of the publication , the database name and the date .
I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .
Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?
Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with: When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted). So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories? For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?
I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries
E.g. for id name ="PSK_30s1207681L002" AND median = 5 we have four entries
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
I have a 3 tier replication with one master server on tier1, 3 servers on tier 2 and up to 20 remote servers (tier 3) on each of the tier 2 servers. I recently had to delete the subscriptions to each of the tier 3 servers from the tier 2 servers and then recreate the subscription. All went well for most of the subscriptions, but for 3 subscriptions the subscription was dropped at the tier 2 level but failed to drop at the subscriber due to a time out. I went to the tier 3 servers in question and issued the sp_removedbreplication command that supposedly executed properly. On one of these 3 servers everything worked fine and I was able to re-subscribe successfuly and have everything continue to work properly. On the other 2 it appeared the re-subscription worked properly until users began to try to update the database through adds and updates (all controlled through stored procedures). Users receive an error message claiming an invalid object name ctsv_ followed by guid looking string of characters. I suspect it has to do with all of the stored procedures that were created by merge replication when the subscription was set up and initialized (no sync). These include numerous sp_ins_, sp_sel_, and sp_upd_ ... named procedures and a bunch of dt_ ... stored procedures as well.
How do I clean this up so that I can have users using their database again and then re-subscribe again short of rebuilding the database (a very time consuming process)?
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL ------- SELECT job.job_number, (SELECT MAX(jsl.job_log_number) FROM job_status_log jsl WHERE job.job_number = jsl.job_number AND jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
Hi All,I am trying to delete rows from a table using a SQL statement similarto this:DELETE FROM TableA where ID1 IN(Select ID1 From TableB where ID2>= @min and ID2<=@max)Basically I want to delete all rows from TableA that have an ID in arange in TableB. This is done in a stored proc.When I look at the execution plan, it is using the indexes as I wouldhope for. The problem is that it is doing a sort which accounts for73% of the cost. I do not need to sort the results. I don't care whatorder they are deleted in.How can I prevent the sort from occuring? I need this delete to occuras fast as possible.Thanks In Advance
Hi i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table. regards vinod.t.v
Hi all, I have a stored procedure that generates the following SQL WHERE clause UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND It is good except that it i can not remove the last AND which is not neccessary at the end of the clause. I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
I've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
Help
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection") Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
I have lot of SQL logins which do not have access to any databases . They do not show any database access when i check their properties . My server is SQL 7.0 with SP 3 .
I want to clean up those logins with the help of script instead of deleting every login manually which is time consuming as i have to check the database access before deleting the login.
Is there any where script available or any other way to delete these unnecessary logins ? Also will it be a problem on the SQL Server if i delete those logins at one command ?
Here is a simplified version of my problem: I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully? Marcha x
Hello, I have a report that is based on a dynamic column. The column is specified by the user, and the column tyep may be text, datetime, or bigint. The user specifies the column name, and there is also a parameter that specifies the type. I allow my users to format the date via passing in a date format string, so my code looks something like:
=Switch
(
Parameters!primarycolumntype.Value = "text",
Fields(Parameters!primaryitemcolumn.Value).Value,
Parameters!primarycolumntype.Value = "date",
[DO DATE FUNCTIONS HERE, e.g. Year(Fields(Parameters!primaryitemcolumn.Value).Value)],
Parameters!primarycolumntype.Value = "numeric",
Fields(Parameters!primaryitemcolumn.Value).Value
)
The trouble I am running into is that I am getting invalid date conversion errors whenever a text value is being supplied - in other words, the code in the "DO DATE FUNCTIONS HERE" is being executed regardless of whether my primary column type parameter is text OR date. It would appear that all conversion fuctions are calculated at runtime prior to running other logic. If so, this is a bad thing for me, as that means I cannot mix data types in the same report and figure it out all dynamically. Is this true? I had always thought that the date code (in the example above, the call to Year()) would not be executed unless that statement in the switch statement was true. I've had the same luck with If statements, and Choose statements, and I've even tried using custom code.
I think my whole problem relates to a misunderstanding of how the functions are processed. Can anyone shed some light?
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months: -- Purge records in small portions to avoid locking production tables -- for a long time. The process takes longer, but can co-exist with -- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45 Incorrect syntax near 'Failed:'.
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....
Here is the code...
SELECT * FROM TABLE
order by case when isnumeric(fieldname)=1 then right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10) else fieldnameend --10 is the length of the field
The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....
I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1
I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...
DBCC Shrinkfile (DB_Name_log2, Emptyfile); go alter database [Db_Name] remove file DB_Name_log2; go
I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?
I have a need to create a report that has a graph at the top and a table at the bottom. The graph at the top can optionally be made hidden because they cause problems when exported to Excel as images. However, when I set the Hidden property of the graph to true, positions of all items on the report remain absolute. Meaning of course that the table that is located half-way down the page remains half-way down the page and there is a lot of nothing on the first half where the graph used to be.
It would be desirable to have the ability to have the table move up when the graph is not visible, however it obviously doesn't do it automatically and also refuses me the ability to change the position with an expression.
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
This a microsoft SQL 2000 server. I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID. Here is my problem: Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name FROM dbo.sysobjects WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
I have a third party app that passes parameters (main dataset query) from the app to SSRS. Â Unfortunately, when the parameters are passed from the app they will contain equal signs ("=") in front of each parameter. Â For example, the parameters that may be passed to the main dataset query should be:
"HYDRO, OKO, ONEPL, TECHNI"
However, the parameters that are passed from the app, get to SSRS as:
"=HYDRO, =OKO, =ONEPL, =TECHNI"
Again, this is for the main dataset query and there may be one parameter or there may be any number of them.
Basically, I need to strip the "=" signs from the parameters. whether there is one parameter or ten.
I believe that I will need a custom function to strip the equal signs?
Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's. This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber Customer 1 - Phone number 1 Customer 2 - Phone number 2 Customer 2 - Phone number 3 Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB. How much for MS SQL in max. entries and how much in GB?
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:
Select L.URN, LH.THE_TIMESTAMP, LH.ACTION_TYPE, LH.ACTION_BY, LH.ACTION_REQD, LH.NOTE, em.position_type_ref From LOG L Join Log_history LH on (L.URN = LH.LOG_URN) left outer join employee em on (em.code = LH.action_by) Where (L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')
Order By L.URN ASC, LH.THE_TIMESTAMP DESC
The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
I need help desperately. The following is an example of the data that I have.
I have two tables one name Customers and the other named Orders. The tables have the following attributes
CUSTOMER CustomerNum, CustomerName, OrderNum
ORDER OrderNum, OrderDesc, CustomerNum
Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively
The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table I add 1 to the ID and then insert a new record with the new ID into the table.
