I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
View 2 Replieshello,
Can you give me an example of suppression under DOS, of the files with under-directories by exec cmdshell
thank you in advance
Pascal
hi, If I want to make one to many relationship, I put the pk of the parent as a fk in the child . like this
customer table (cust_id, fname,lname)
customer_order table(order_id,cust_id,qty,price)
I do understant this, but what about look up table, can I consider cust_id inthe customer_order table as a lookup field to the customer table?
Thanks
Al
This one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
View 2 Replies View Relateddear all,
I have a case.
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
any idea or suggestion will be great....
regards,
-dedys
Hello all,
Many people are angry at MS for promising and not delivering on the mirroring feature of SQL 2005.
Does anyone have any idea - officially or unofficially when this feature will be available for reliable use, as we would like to use it.
Thanks,
S.
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]....
Hi,
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
Thanks.
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.
If I wanted to make a report that shows different fields, one being cost, but I only wanted certain people to see cost based on their AD groups, is there a way to hide this information?
Basically, domain/user1 belongs to AD purchasing group purchasing, he can see item, description and cost, where domain/user2 belongs to sales so he can only see item and description?
I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?
1)1st method:-
Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.
Parent TAble
Id name
1 XYZ
Child table
id name parent id
1 abc 1
2 qwe 1
2)2nd method
table with id and parent ID. Top level element will have null value in table. eg
id name parent id
1 xyz
2 abc 1
3 qwe 2
4 adf 1
Retrieve data using recursive queries supported in SQL Express.
Which is the best solution to store parent child relationship???
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL.
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
[Code] ....
when shipping data from one place to another, without complicated merge needs, do service broker and replication differ in what they offer?
View 4 Replies View RelatedHi,
We are developing a new oltp application using SQL Server 2000 and are debating whether to use "cascade delets" or not. In earlier apps, we had issues using cascade deletes like someone deleted parent by mistake and all child records got deleted OR SQL Server crashed in middle of cascade delete and records were lost or performance became an issue when there were huge # of child records to be deleted,etc.
Are there any recommendations for/against using Cascade deletes and when to use and when NOT to use cascade deletes ?
Thanks
Satish
I need some suggestions concerning the issue cascading deletes in a self-referencing table, i.e. a table with a foreign key pointing at the primary key in itself. Although SQL Server still does not support cascading deletes the declarative way there are some other ways to handle this situation. One common way is to use trigger coding with the simple structure
CREATE TRIGGER DelCascadeTrig
ON self_ref_tab
FOR DELETE
AS
DECLARE @C_FK xxxxxx
SELECT @C_FK = C_FK FROM DELETED
BEGIN
DELETE self_ref_tab
FROM self_ref_tab, deleted
WHERE self-_ref_tabC_FK = deleted.C_PK
END
where C_PK is the primary key column and C_FK the foreign key column. The problem is that this simple pattern does not work with self-referencing tables, because the removal of dependent rows deeper layer (n-2, n-3 etc. if the originating delete request is level n and the first level of dependant deletes handled by the trigger code is n-1) would require the delete trigger to fire more than once for the same delete operation. As far as I know, in ver. 6.x triggers are executed only once per SQL statement and in this case the n-2, n-3 etc. level rows would have been left as "orphans".
In ver. 7.0 I suppose this should work fine because of the new recursive trigger execution possibility (trigger will fire up to 32 recursive times per SQL statement), but in the meanwhile (i.e. my case util we have upgraded all our servers) the delete logic for a cascading, self-referencing relationship must be handled completely within one execution of the trigger.
My question is now: does anyone know anything about any common algorithm or trigger code example solving this problem.
Thanks in advance
Bjorn Ehnberg
When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
thanks
dog
I'm wondering if anyone has compared the speed of different kinds of deletes.
Assuming super large tables, I wonder, what is the speed ordering for the following operations:
* Remove a column
* Remove a row
* Remove a table
I'll experiment to see if I can get numbers comparing the speeds, but, if someone's already done the work, I'd love to know
thanks
I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables.
People PeopleEmails Email
(pk)peopleid (pk)peopleid (pk)emailid
fname (pk)emailid address
lname emailtype
password
In this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks.
Hello,
I have three tables:
CREATE TABLE TASKS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
NAME VARCHAR(20) DEFAULT '' NOT NULL UNIQUE,
PRIMARY KEY (SEQNO)
);
CREATE TABLE JOBS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
NAME VARCHAR(20) DEFAULT '' NOT NULL UNIQUE,
PRIMARY KEY (SEQNO)
);
CREATE TABLE JOBTASKS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
JOBID INTEGER DEFAULT 0 NOT NULL REFERENCES JOBS(SEQNO),
TASKID INTEGER DEFAULT 0 NOT NULL REFERENCES TASKS(SEQNO),
PRIMARY KEY (SEQNO)
);
When I delete a record in JOBS I would like to create a trigger that would delete the related records in JOBTASKS.
This is my attempt; but it does not work.
CREATE TRIGGER JOBS_BEFORE_DELETE
ON JOBS
FOR DELETE
AS BEGIN
DELETE FROM JOBTASKS
WHERE JOBID = (SELECT SEQNO FROM DELETED);
END;
Thanks,
Steve
I want to use one stored procedure to delete rows in three different tables - how do I write this for Sql Server 2000 please?
PROCEDURE dbo.DeleteSubmission @C_ID intASDELETE *FROM tblCompaniesWHERE C_ID = @C_ID
DELETE *FROM tblStoresWHERE CS_ID = @C_ID
DELETE *FROM tblDistributorsWHERE CD_ID = @C_ID
RETURN
I'm hoping someone has seen this before because I have no idea what could be causing it.
I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.
My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.
The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT
AS BEGIN
SET NOCOUNT ON;
INSERT INTO tblHistory(table_name, record_id, is_insert) VALUES ('items', 123, 1)
END
tblHistory's field types are (varchar(50), BigInt, bit).
As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?
Thanks in advance!
I have a logins table, a loginroles table (intermediate), and a roles table
When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.
Any help would be appreciated.
Thank You,Jason
It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in
View 4 Replies View RelatedHi
I keep data in four seperate locations, each with different data retention.
the problem is, i keep the publisher "thin" - only 24 hrs of data) the data in the relevant tables is about 250000 a day) and these deletes are blocked at the subscribers by commenting out the body of the repl proc.
this does however cause a burst in bandwith & locks the subscribers, even though no actual delete takes place.
does anyone know if you can actuaklly NOT REPLICATE delete commands for specified tables at all - so that the message of the delete wont even be SENT to the subscriber?
Des
Hello everybody,
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashish
Hello all.
Please help me shed light on the following problem:
I am deleting from 2 tables in a transaction. I experience a deadlock occasionally when deleting.
Here are the tables:
create table TABLE_1 (
ID integer not null,
TYPE_CD varchar(50) not null,
STATUS_ID integer not null,
NAME varchar(500) not null,
COMMENT varchar(500) null,
CREATED_DATE datetime not null,
CREATED_BY varchar(15) not null,
LASTMOD_DATE datetime null,
LASTMOD_BY varchar(15) null,
constraint PK_TABLE_1 primary key nonclustered (ID)
)
create table TABLE_2 (
TASK_ID integer not null,
TABLE_1_ID integer not null,
COMMENT varchar(500) null,
constraint PK_TABLE_2 primary key nonclustered (TASK_ID, TABLE_1_ID)
)
Table table_2 is an intersection table between table_1 and another lookup table (called task).
Whenever I delete from table_1 I need to delete from table_2 to maintain integrity.
The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).
In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete.
In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:
select *
from table_1 WITH (UPDLOCK),
table_2 WITH (UPDLOCK)
where table_1.id = table_2.table_1_id "
and table_1.id = ?
and table_2.task_id = ?
I then issue my deletes in the following order:
delete from table_1
where task_id = ?
and table_1_id = ?
and then,
delete from table_1 where id = ?
The application sets the isolation level to READ COMMITTED.
The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end.
When the deadlock occurs these are the locks that are held by the running processes:
Blocked SPID (10):
============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c)
app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3
app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443
app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157
app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea)
app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252
Blocking SPID (12):
=============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3
app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252
app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176
I could not figure out what the problem is. Please help me with this.
Any help will be much appreciated.
Thanks in advance
Hello all. We are just getting started with replication and I'mwondering if there is a way to not have deletes replicated. I know Icould manually remove the delete trigger from every table but I'mintrested in have this a bit more automatic (like when its creating thesubscription).ThanksStephen
View 1 Replies View RelatedI have the following tableCREATE TABLE [tbl_Items]([item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_idPRIMARY KEY,[parent_id] int DEFAULT(NULL) CONSTRAINTFK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ONDELETE NO ACTION ON UPDATE NO ACTION)My Intention was to create a table that when I delete a record, allrecords that have on the [parent_id] field the deleted record[item_id].I am trying to avoid having to use triggers or create a storedprocedure that firsts delete the children (recursively) and thendeletes the parent.Is there any way to do this by changing my table definition here?
View 1 Replies View RelatedHi, I'm using SQL server 2000, and I have set up two tables, table Aand table B. Table A and B have a foreign key constraint such that ifan entry is deleted in table A, then all the entries in table Bassociated with that entry are deleted as well. I imagine that I canfind out the number of records that are deleted in table A when Iexecute the SQL, but is there an easy way of determining the number ofrecords that will be deleted in table B?Thanks,Dan
View 1 Replies View RelatedHi guys,
A simple question of good practices: is it better to have a cascade update/delete on a table? Or is it better to do it "by hand" in a SP?
Thanks a lot.