I have maually deleted the Database.LDF file by stopping the SQL Server. When i start the SQL Server, merge replication which was configured for the database does not work. How do i fix this problem.
Thanks in advance.
Regards,
Venu.
I have scheduled a job in Management Studio, but it doesn't work. However, when I run it maually in Visual Studio it works. I have connected an outside server by mapping it to mine. Maybe this is the problem?
I have also tried to configure a linked server, but I cannot find out how to connect my SSIS package to the linked server.
Can anybody help me?
Thank's!
Hi,
i defined a sqldatasource in VWD manually (option specify sql statement) because several tables are involved. I also need a Delete statement, so i defined it also manually.
The select and delete statement are :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>" SelectCommand="SELECT aspnet_Users.UserName as lid, aspnet_Roles.RoleName as categorie, aspnet_Users.beheerder as beheerder, aspnet_Membership.Email as emailadres FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId inner JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId order by username"
DeleteCommand="delete from aspnet_users where userid=@userid"> <DeleteParameters> <asp:Parameter Name="userid" /> </DeleteParameters> </asp:SqlDataSource>
My problem is that the Select works, but not the Delete.
Any idea why?
Thanks
tartuffe
I've been studying/experimenting with the replication features and though the Updating Susbcribers Replication Model was the answer to my data environment.
What we want: We have a live server (Publisher) that'll serve content to our users, but we want an "offline/data entry" server (Subscriber) that'll pretty much have the exact same data as the live server for data entry and validation purposes. Changes and inserts would be done on the Subscriber locally, vetted through, and once done, it'd be updated to the Publisher.
I've managed to get some semblance of the above working, but the updates are nearly instantaneous. Which means that changes/inserts that may be erroneous on the subscriber machine would almost always be seen on the publisher machine before our operations can do anything about it.
Is there a way to configure the replication process to hold off until the admin triggers it? Or am I using the wrong feature?
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
Hi,
I thought my problem was a LINQ problem but I got referred to this forum, because they didn't think so. I'm kind of new to all these different forums so I will just try it over here (don't know if I'm in the right place).
My application is suppose to delete all the rows in all the tables and after that it should reseed these tables. I'm using LINQ and I think that's where the problem occurs.
Firstly I execute queries like these for all 5 tables:
dataContext1.ExecuteCommand("DELETE FROM PRODUCER");
After that I immediately execute the following on all 5 tables:
dataContext1.ExecuteCommand("DBCC CHECKIDENT (PRODUCER, RESEED, 1)");
The problem here is that a newly inserted row will not start at 1 in any table. It just continues where it left of before the deletions. However when I execute the empty database function twice (without inserting any rows in between, so I execute a delete on every already empty table) it IS reseeded. I also tried to use different DataContext instance for the reseed queries, but no luck there either.
I think the TRUNCATE command is no solution to my problem because I have a couple of Foreign Key Constraints in my database.
Does someone know the answer to this problem?
Thanks,
Yoni
I have a data flow that reads from a flat file source, goes through one data transformation component to change from unicode to normal text and writes the data to a SQL Server table. This has been working fine throughout development using a specific source file as input. I have now manually changed the path and name of the input source file in the connection manager to point to a new file and the task continues to process the old text file.
I open the connection manager and check its properties and preview the data and it all looks fine - it is finding the new file. I also edit the flat file source component and preview the data and it shows the data from the new file. I run the data flow by right-clicking and selecting Execute Container and it continually reads the old file and processes it! (I do the right-click thing because this is just one small part of a larger package.)
This has got to be a bug, but just where I wonder. Anyone ever see this before? I'm going to try to run the entire package in debug mode, instead of right-clicking, next and see if that's any different. Anyone have any ideas on how to force a refresh of the necessary internal components to make it read the new file? All the external properties point to the new file, but it's not being read.
Joe
Update:
I ran the entire package and found no difference in execution - it's still reading the wrong input file. I then deleted and recreated the connection manager, again specifying the new file to read. It still continued to read the old file. I deleted the flat file source component and recreated it, specifying the latest connection manager (twice, since I must have pointed it at the wrong one the first time and it read a completely different file). I still have the same problem of it reading the wrong input file. I don't know what else to recreate that would have any effect. Does anyone have any ideas? I need to change the pointer to different files multiple times and have it read several different input files. This has got to work somehow. Any help is appreciated.
Joe
I am creating a SSRS report which would be executed by User manually through ReportServer URL.User would be generating the SSRS report for different Customer ID based on ad-hoc basis.
I am passing CustomerID as input parameter to the report. Is there any way to get the manually generated SSRS report name as 'Report_CustomerID_TodayDate.xls'.
E.g.If User is generating report for Customer ID 123 today then report name should be 'Report_123_07092013.xls'
I am looking for an opinion on the best way to delete all data from a table after it has been replicated.
The simple use case would be:
Data is replicated from production server to archive server.
Data is deleted from production serverThanks in advance...
Here's what I'm trying to do (Maybe there's a better way)
I'm using Transaction Replication and a pull subscription to get all the transactions from the ORDERS table within the last year from Server_A to Server_B. I filter the rows "WHERE (ORDR_DATE > (GETDATE()) - 365)"
That works fine, BUT, the old rows in the subscriber's ORDERS table do not get deleted, so I have all the records for the last year, plus the older records that I don't want any more. Do I need another step some where that does a "Delete WHERE (ORDR_DATE < (GETDATE()) - 365)" But then I have to maintain my date logic in more than 1 place if I ever want to change it.
Any suggestions, thoughts, improvements will be appreciated .... Thanks a lot
Ok so I have some dynamic sql to delete a file that is created via sql earlier on. It is to provision a copy of a database to an instance on link server. Everything works great and the files used to delete. Now, with no code changes it is throwing a syntax error. I do a print of what the dynamic sql is creating before executing and then I copy / paste what was generated into command prompt and guess what!! The file deletes.
Here is the result on screen:
@DeleteBackupFileStatement: DEL adas16.clients.advance.localwip$AvionteAP_Template_893.bak /Q
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
Here is the code that creates the statement:
SET @DeleteBackupFileStatement = NULL
BEGIN
SET @DeleteBackupFileStatement = 'DEL ' + LTRIM(RTRIM(@BackupFile)) + ' /Q'
END
PRINT '@DeleteBackupFileStatement: ' + cast(@DeleteBackupFileStatement as varchar(400))
BEGIN
EXEC adasdb.master.sys.Sp_executesql
@DeleteBackupFileStatement
END
END
The value of @BackupFile is simply the path of the file with the file name, everything in the prepared statement with the exception of DEL and the switch at the end.
Hello,
I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged.
Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me.
Corey
I am running a simple merge replication in SQL Server 2000. I have one database that is the publisher, and a second database that is the subscriber. When I add a new row to the subscriber it will replicate to the publisher as expected. However, the new row at the subscriber will then be deleted without explanation. The row will remain at the publisher though.
Does anyone know why it is doing this?
I have 1 sql server 2005 database in Server A that is publishing a table to another database in Server B using the transactional method.
It is possible for me to delete all the rows in the database located at the publisher at the same time the subscriber pulls them?
Thanks.
How can you delete a log file.
Say I have backed up a transaction log at some drive say G:.
Now I want to delete this log file and want to define it in a job.
What is the syntax -
delete G:Test_log.bak ????
What is a proper syntax??
Thanks!
I have a database, whose size is 1600MG. That database needs to be copied to the other server. But, the server does not have enough space (1600MB) for the database since the other databases also share the space. Therefore, I transfered the database to a database in that server whose size was set up 700MB the server. The table schema and data were transered successfully. But, when I tried to replicate the tranfered data to another database. The transfered database did not show all of the tables which were shown when I had queried them against that transfered database.
My question is that why it did not show alll of the tables for me to pick up for replication. Does that have something to do with the transfered database? In other word, if I set up the same size of database for the transfered database, it would have worked. Right?
I'm pretty new to the whole replication method. I've been making c++ programs to replicate the databases using manual odbc classes. I'm ready to try something new if it would work for me.
I have multiple databases. Basically I have a bunch of computers, each with a SQL server. These databases are the "publishers" due to the fact they are the databases that receiving new data. All of the publishers then upload their new information to the main SQL server. This seems not an issue but the trick is when the publisher uploads the information to the new SQL server, it then deletes the uploaded information from itself. This is basically the same model for everything I do. I was hoping someone might be able to tell me how to do this with replication/triggers and what versions of SQL Server 2005 that I would need to put on each of the publishers and subscriber (main).
Thank you,
Jeremy
can some one please help me to delete a File with extension .mdf, as its continously giving error that file can't be deleted its being used by any other program or user.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
I am trying to remove the transaction log file since I have created another one on a seperate drive by it wont let me do it . I get this error "cant delete the primary data or log file"
Now microsoft site clearly state steps for deleting the transaction log file , by first emptying it and then deleting it BUT IT DOESNT WORK.
I have even tried detaching the database and then removing the transaction log file and then attaching the database again BUT it attaches the transaction file back again GRRRRRR
then on top of this MS SQL studio does not allow to put PRIMARY or anyother value to the Filegroup for tansaction log files so how on earth can a transaction file be "primary" as the error is stating ??????
can anyone tell me how to get rid of a transaction log file that was created with the database ?
Hi All
Was getting this error
Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
So went to EM & added another file (Additional file in some other drive ) to the Tempdb , now I want to delete this file ???
What are the steps ... I cant re-start the SQL Server
Please help .
Hi,
I can not delete this file from C/Windows/Temp directory, all other files form that foder I can delete. The error message I get is that something else is using this file. The thing is I cant find what is using it, even though I uninstaled SQL Server 2005 completely. I have tried lots of things, nothing helps. At the end I entered windows in SAFE MODE, went to Computer Management and disabled almost every service I can, that might be using it, and still cant get rid of this SQL.LOG which is 26GB now. The only thing I could do is to compress the file and now it is 6.5GB.
Txs for the help..
I set up the transationsal replication on SQL7.0. It did not work. Here are the messages
1 on Snapshot Agent: The process could not create file Server_namec$MSSQL7REPLdATAFTPServer_namefile_nam e. Error: Access is denied
2. On Log Reader Agent: bi replication transaction are available
3. Disribution Agent: The initial snapshot for publication "DB_Name" is not yet available.
Thank you for your help
hi guys :( ive searched on all the topics here about replication. but now i am wondering what windows xp settings do i have to edit? i read something about dcom settings and changing permissions. what is the RIGHT way to share a file or whatever because the schema cannot be accessed for some reason. i need a whole list of things to do because things are not working out after i read the tutorials on other sites. please someone help me.
basically i need to have two servers that need to be replicating each other. i realized that the instances have to be named so i am just totally confused. ill even let anyone connect remotely to help me out if they want. or explaining will do fine. please! thanks guys!
happy holidays!
Hi ALL,
I need to delete 1st and last lines of text from a text file in an automated process,and save the file with the same name or a different name at the same location. How is that possible?Any help is greatly appreciated.
Thanks!!
Sai.
In SQL 2012 I'm trying to delete a log file (both physically and logically). There are two log files and one of them is unnecessary. When I click 'remove' in SSMS it will delete the log file, but then when I go back under database properties it's still showing up even though the file has been physically removed from the OS. I'm wondering what steps I can take to get rid of the file permanently?
View 12 Replies View RelatedWhen I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.
Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).
I have a flat file that look like this
############################################################
# Market Issue Lookup
# Applies to: Muni,Pfd,Govt,Corp
###########################################################
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|
############################################################
# Maturity Type Lookup
and i want it to look like this
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|
Basically remove any line that start with a "#" or any blank lines..
I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful
Thanks for any help in advance.
smathew
Hi there, the subject says it all.
I have two databases on one server. I want to replicate 1 table from one Dbase to the other. Can it be done? Am running SQL 6.5 on NT 4
Gordon
I use discountasp.net. I've added some new tables that I'd like to place on my website. My problem is that if I reattach my mdf file to my server, my member roster gets deleted. So my question is how can I update the mdf file without losing member information?
Thanks,JW
Hi,
I'm new to SQL Server 2005 (up until now I've been an Oracle DBA exclusively) and have been tasked with backing up all (6) of our SQL Server 2005 databases on a regular basis. To accomplish this, I've created individual SQL Server jobs for each database I wish to backup. Each of these jobs executes a T-SQL script like this:
BACKUP DATABASE [MYDATA] TO DISK = N'\networksharemydata.bak' WITH RETAINDAYS = 4,
NOFORMAT,
NOINIT,
NAME = N'MyData-FullBackup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
My goal in specifying 'RETAINDAYS = 4' was so that there would always be 4 days worth of "backupsets" (forgive my Oracle-ese) kept in mydata.bak. But when I look at the amount of backupsets kept in mydata.bak I noticed that all of them were being kept and none were being deleted.
I researched this problem a little bit and thought I had discovered a solution by specifying 'INIT' instead of 'NOINIT' and 'NOSKIP' instead of 'SKIP', but this deletes the entire contents of mydata.bak only-if all of the backupsets contained in it are past their expiration date. I was hoping it would just delete the expired backupsets and keep the non-expired ones, but this isn't the case.
Does anyone know the simplest way - with T-SQL commands - to accmplish the task of backing up a database to 1 individual .bak file and also only retain x amount of "backupsets" within it? Any help would be greatly appreciated, and the more detailed the better. Thanks.
- Gary
Hi,
I'm new to SQL Server 2005 (up until now I've been an Oracle DBA exclusively) and have been tasked with backing up all (6) of our SQL Server 2005 databases on a regular basis. To accomplish this, I've created individual SQL Server jobs for each database I wish to backup. Each of these jobs executes a T-SQL script like this:
BACKUP DATABASE [MYDATA] TO DISK = N'\networksharemydata.bak' WITH RETAINDAYS = 4,
NOFORMAT,
NOINIT,
NAME = N'MyData-FullBackup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
My goal in specifying 'RETAINDAYS = 4' was so that there would always be 4 days worth of "backupsets" (forgive my Oracle-ese) kept in mydata.bak. But when I look at the amount of backupsets kept in mydata.bak I noticed that all of them were being kept and none were being deleted.
I researched this problem a little bit and thought I had discovered a solution by specifying 'INIT' instead of 'NOINIT' and 'NOSKIP' instead of 'SKIP', but this deletes the entire contents of mydata.bak only-if all of the backupsets contained in it are past their expiration date. I was hoping it would just delete the expired backupsets and keep the non-expired ones, but this isn't the case.
Does anyone know the simplest way - with T-SQL commands - to accmplish the task of backing up a database to 1 individual .bak file and also only retain x amount of "backupsets" within it? Any help would be greatly appreciated, and the more detailed the better. Thanks.
- Gary
Hi,
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
- <Configuration ConfiguredType="Property" Path="Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:APPSBACIARSalesforceConnectSalesforceConnect.log</ConfiguredValue>
</Configuration>
Appreciate a help.
Gulden
I want to get notice by netsend, email or any other methods
when replication doesn't work or malfunction.
Does anyone know how to do this or any other solution you use for this kind of issue?
Thank you..