I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
View 2 RepliesAll is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:
The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:
select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].
sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
select *
from sys.database_permissions
where grantor_principal_id = user_id ('NetworkSERVERNAME$'
--
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65538 0 5 19 SN SEND G GRANT
And more 14 rows…
resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
Thanks in Advance,
Phillip M. Triocli
Hi There
View 4 Replies View Related
I'm in a middle of a project where replication established between Oracle 8i and SQL server 2005. Now we need to drop one column from on of the tables replicated from Oracle side. My question : Is there anyway I can do this in Oracle and automatically reflected on SQL server? If not what is the proper steps to do it. Realy appreciate your fast response.
Regards
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
View 1 Replies View RelatedHello,
I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
Any help or scripts would be appreciated!
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedThis morning I found that during the night someone hacked into my remote hosted SQL server 2005 database. Every record in a description field in each of three tables had everything after the first sentence replaced with the same URL. The three pages that used these tables badly infected anyone who viewed them in IE.I had my host restore the 12/30 backup, and I changed all the database and ftp passwords, now I want to be sure my code isn't giving anyone access. I'm using strongly typed datasets. This is my code behind code.Dim sCategory = Request.QueryString("cat")Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapterDim dF As Data.DataTable = ResourceAdapter.GetDataByCategory(sCategory)Dim numRows = dF.Rows.CountLabelCat.Text = sCategoryRepeater1.DataSource = dFRepeater1.DataBind() Is there anything I can do to better secure this? Oh, my connect string is in the web.config file only, but it isn't encrypted. I will be encrypting it today.Diane
View 9 Replies View RelatedHi all.. I've been scouring the forums for about 6 hours to no
avail. This is a really simple question. I'm trying to have
a registration page that lets the user input name, email, desired
username, and password. I want to check the username and email
fields to make sure ppl cannot sign up twice. So from what I've
gathered I have a couple of options:
1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
Hi
I have a problem. Every time I select the first Item of a dropdownlist, it prevents the Insert to the database. There are 8 droplists on the page but the insert is only effected by the First Item of lstTheme selcetion.
My Insert code is here: Any ideas??
Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1
Dim Name As String
Dim values As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, DropDownList1, txtDate"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date) VALUES (@themeValue, @guideValue, @guidepageValue, @pagetypeValue, @changecategoryValue, @changefromValue, @changefromtextValue, @changetoValue, @changetotextValue, @contentmanagerValue, @dateValue)"
SqlCommandThemeTest.Parameters.Add("@themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@contentmanagerValue", DropDownList1.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@dateValue", txtDate.Text)
Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try
Response.Redirect("WebForm3.aspx")
End Sub
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
View 1 Replies View RelatedI have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?
Thanks in Advance - SOX is killing me!
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
Any help would be much appreciated.
we're in a business where customers often ask for a foolproof scheme that even prevents folks with DB privileges from fraudulently inserting, updating or deleting data. The scheme must be so air tight that a judge can be convinced of its reliability.
What are the most effective schemes out there?
We are running SQL Server 2014 Web Edition (64-bit). On three occasions now we have had two of our live secondary log shipping databases become unusable through the log shipping process. We ship logs to these databases every five minutes, and generally the process works fine. However, on three occasions in as many months we have suddenly received alerts warning us that the transaction log restores could not be performed on the secondary databases. The SQL Server Error Log revealed the following:
Date,Source,Severity,Message
10/13/2015 04:01:16,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Starting up database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Recovery is writing a checkpoint in database 'ObfuscatedName' (8). This is an informational message only.
[code].....
An error occurred during recovery, preventing the database 'ObfuscatedName' (8:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support."
The extract above shows two previously successful restores to the secondary database. Suddenly, at 04:11:16, the database can no longer be started up or restored to. This is despite CHECKDB giving a clean bill of health only five minutes earlier. The result is that the last three lines are then repeated ad infinitum.
The only way we have managed to get around this issue so far is to replace the secondary database with a recent backup of the primary. Once this is in place, any outstanding transaction logs are successfully applied when the log shipping restore job next runs. However, this seems rather drastic.It is rather concerning that our warm-standby databases continue to become corrupted without any apparent explanation from SQL Server.
Hello,
I want to create one more publication on a database (SQL Server 2005) that is already a publisher and has subscribers. The subscribers of the second publication will not be inserting new records so I will mark the articles (tables) as download-only.
Is there anything extra that I should take into consideration? Are there any side effects of this process?
Thanks in advance.
SilentOceaner.
Hi ...i am new to sql server replication, im using the sql server 2005.
I was configure the replication n publish a database , i wanna add a new subscription, but i am facing the follow error
You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
How do i add another subscription database,since in my sql server has only 1 subscriber?
Hi,I am trying to use Enterprise Manager Wizard to create a newPublication database but I got the following message.Error 2812: Could not find stored procedureMyDB.dbo.sp_MSadd_logreader_agentThere seems that some replication stored procedures.Any help?Thanks in Advance
View 1 Replies View RelatedWhen I tried to restore backup of publication database, time taken for restoration is too long [ 4 hours]. The actual database restore takes only 20 minutes but the stored procedures used for cleanup replication takes the remaining time. Is the issue related to my environment or the cleanup replication stored procedure?
View 7 Replies View RelatedHi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View RelatedI'm having a weird problem with an easy process. I have a section that allows people to enter their name and email address if they wish to be contacted. I have it set up so that when they enter that information into the text boxes, the info is then sent to my SQL database. The code looks right to me, but it never comes up. Here is the code I have for the button_click.
Dim NTAdatasource As New SqlDataSource()
NTAdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
NTAdatasource.InsertCommandType = SqlDataSourceCommandType.Text
NTAdatasource.InsertCommand = "INSERT into ContactUs (YourName, EmailAddress, DateTimeStamp) VALUES (@YourName, @EmailAddress, @DateTimeStamp)"
NTAdatasource.InsertParameters.Add("YourName", txtYourName.Text)
NTAdatasource.InsertParameters.Add("EmailAddress", txtEmailAddress.Text)
NTAdatasource.InsertParameters.Add("DateTimeStamp", DateTime.Now)
Dim RowsAdded As Integer = 0
Try
RowsAdded = NTAdatabase.Insert()
Catch ex As Exception
Server.Transfer("problem.aspx")
End Try
If RowsAdded <> 1 Then
Server.Transfer("problem.aspx")
Else
Server.Transfer("success.aspx")
End If
End Sub
The strange thing is that when I debug, the "rowsadded" value comes up to 1. The process runs through debugging just fine and redirects me to my "success.aspx" page. What am I doing wrong?
Thanks
I work with multiple servers and I have system administrator rights on all server. My question is, why on some server when I click on a database it shows the General, Table & Index, Space Allocated tool bar, than if I go to a different server, click a database it does not show? I would like to see this toolbar on all my servers when I click a database.
Thanks Reggie
I want to write a sql query for information from a table in different database but on the same server.
what is the syntax.
select * from databse2.owner.table ??
i am in application1 database and i want to query from application2 database on the same server.
use application1
go
Select * from application2.dbo.tablename
go
Is this query correct?
Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
Thanks,
Mary Elizabeth
NW Natural
Hello,I am trying to use the SQl Server database reporting tools to create areport on the database to find areas for optimization including tableformats, column formats, and sizes. Can anybody please help me withthis. If any body has used any other tool by which I can create abovementioned reports plz do let me know. I will be really thankful foryour help.ThanksSuneel
View 1 Replies View RelatedI have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete.
Thanks, Steve
anyone know how to drop my databse on sql pleasee
charles
I am trying to drop a database, but keep getting the following error."cannot drop database 'blah' because it is currently being used forreplication".This db is not currently being replicated, but once was. It is thesubscriber side of an old replication pair.Can anyone tell what I have to do to make this go away?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHello everyone!I'm from El Salvador and i have a problem, i have a sybase systemrunning on windows 2000 server, probably a programmer drop adatabase...but i dont know who did it?, is it possible to know who dropthe database?, if the answer is yes... plese help me, because we need tosolve this security problem in our company.Sybase saves all the TSQL and transaction somewhere?,Saludos....--Posted via http://dbforums.com
View 1 Replies View Related