SQL 2012 :: DQS Built In Login Delete
Dec 4, 2014
I just started at a new job and they have DQS set up on our BI server. Some genius decided to delete [##MS_dqs_service_login##] login, the user dqs_service still exists in the DQS_Main db. The error log is full of errors similar to this one...
The activated proc '[internal_core].[ParallelExecutionActivator]' running on queue 'DQS_MAIN.dbo.ParallelExecutionRequestQueue' output the following:
'Cannot execute as the database principal because the principal "dqs_service" does not exist, this type of principal cannot be impersonated, or you do not have permission.'I have tried the standard exec sp_change_users_login 'Auto_Fix' with no success. At this point I don't think anyone is utilizing DQS.
View 1 Replies
ADVERTISEMENT
Nov 10, 2014
I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4 'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me?
SELECT
TT.PartNumber
,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]
,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])
FROM dbo.vt_TroubleTicket TT
WHERE TT.Closed = '-1'
and TT.DateClosed between '1/1/2013' and '1/1/2014'
and (TT.PartNumber = '12345')
GROUP BY TT.PartNumber
View 3 Replies
View Related
Mar 18, 2015
Has ever built a separate server just for SSIS and how did you do it?
View 2 Replies
View Related
Jul 23, 2014
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
View 4 Replies
View Related
May 17, 2015
As I am have query, I want to find sqlserver 2012 user/login last login date, when user last time date logon to server.
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>
View 6 Replies
View Related
Dec 6, 2006
I have a database user (Maximum). Its default schema is db_datareader. It was created without a login name. When I try and delete this user, i get the following error message:
Drop failed for User 'maximum'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
How do I remove this user?
View 4 Replies
View Related
Sep 1, 2015
I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "
Same problem facing on different servers.
Note : Environment is SQL 2012,SQL 2008 including cluster servers .
View 2 Replies
View Related
Mar 7, 2007
Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below
by the way I am a member of the sysadmin group
thanks in advance
km
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
View 12 Replies
View Related
Nov 3, 2014
How can I delete user who own a schema in the database? The user is created by myself but I want this to be deleted.
View 2 Replies
View Related
Jan 23, 2015
I am creating a Scheduled JOb that will delete backups older than 3 days old, however I seem to be missing something in my script.
DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -3, GetDate())
EXECUTE master.dbo.xp_delete_file 0,N'E:BackUps',N'bak',N' + @DeleteDate + ''
View 5 Replies
View Related
Mar 28, 2014
I want to know more details about the Clustered Index Delete. Is that Clustered Index Delete in the execution plan is good or bad or we can neglect that cost. Is there any way to avoid that clustered Index delete operator from the execution plan.
View 4 Replies
View Related
Jun 11, 2014
Is there anyway I can turn off delete in SQL server? I want to prevent anyone inadvertently deleting rows in tables. I thought worse case I could have triggers on tables to perform roll back.
View 3 Replies
View Related
Nov 20, 2014
I'd like to delete A3 from the XML variable below, however I get "XQuery [modify()]: The XQuery syntax '/function()' is not supported."
DECLARE @XML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
<A3>6529</A3>
</Attribute>'
DECLARE @n VARCHAR(100) = 'A3'
SET @XML.modify('delete (/Attribute/sql:variable("@n")[1]) ')
SELECT @XML
View 3 Replies
View Related
Jun 8, 2015
I have a delete trigger on one table. When I delete a single row in this table that deleted record will be recorded to a history table.But sometimes my delete will delete more than one record. In this case also only one record is being recorded into the history table.How can I record all the deleted records into the history table when multiple records are deleted with one single delete statement?
View 4 Replies
View Related
Mar 17, 2014
I will try my best to explain this, We have a shopping cart on our website, the person that was developing this has now left the company and I've been given the job to finish it off.
When I load all the items that the user has entered in to his/her cart I return the Item ID and the RowNumber (ROW_NUMBER() OVER (Order by Id) AS RowNumber)
I'm trying to delete the item from the table using the following query
DELETE FROM [dbo].[Cart.Items] WHERE UniqueID = UniqueID and ItemID = @ItemID and @RowNumber IN (
SELECT ROW_NUMBER() OVER (Order by Id) AS RowNumber)
Now the reason we are using the RowNumber is because the user can add the same Item as many times as they like so for example you buy 3 different mobile phones, and you want three screen protectors, they will click screen protector 3 times which will add 3 records in to the db with the same id. so the row number is used to find the correct one.
But the above delete is not working.
View 1 Replies
View Related
Mar 18, 2015
I create a main program which will launch two jobs at a time, each job does some processes and at the end I'm trying to delete those jobs after storing the job details in one of the custom table I created (cleanup sub-program).
Out of two jobs I am able to store one job details (like job_name,job_id,start_time and end_time of the job) in the custom table and able to delete that job, but the job that's getting completed at the end is not getting captured nor getting deleted from sysjobs and sysjobhistory tables.
I had included this step (which will call the cleanup sub-program to store the job details and delete it) at the end. I can see that this cleanup procedure getting called from debug message but it is neither storing details nor deleting the job.
When I execute this cleanup program separately, it does store the job details and delete it.
View 0 Replies
View Related
Apr 22, 2015
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And the same thing the next day and everyday. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. Some tables have 200Mil+ rows. So it takes forever.
View 1 Replies
View Related
Jun 24, 2014
I am not able to login to the SSMS, while I am logging I got this error, how to troubleshoot..
Cannot connect to LAASYA.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
[URL]
------------------------------
The system cannot find the file specified
------------------------------
BUTTONS:
OK
------------------------------
View 6 Replies
View Related
Mar 30, 2015
I set up a trigger to capture logins at the server level. This is part of some metrics I am putting together to properly set up load testing.
I am not surprised by the number of logins from the application logins, but what does surprise me is the number of logins from the various users that can query the production data.
As an example, my account has 1200+ logins for today only.
My first thought was that a process was set up to use my login by mistake. This also applies to some of the other users.But, there are no processes that are using any login except for the SQL service account or the applications logins.
What constitutes a login event?
[URL]
View 0 Replies
View Related
Jul 22, 2015
SQL Server 2012 Ent. Two node cluster with SP2.
Getting the attached error while creating login.
Login gets created even though the error pops up.
View 4 Replies
View Related
Apr 4, 2014
I have an issue with Delete statement.In the code given below (its a part of actual proc),if we use TRUNCATE to clean the temp tables, everything goes fine.But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'I am not able to understand this behavioral difference between DELETE and TRUNCATE.Recently the database is being used for replication, but that should not be a reason.
SELECT @max_rows = COUNT('X') FROM #temp_table1
SET@row_cnt = 1
WHILE @row_cnt <= @max_rows
BEGIN
[code],...
View 2 Replies
View Related
Jun 9, 2014
My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.
For this one I need to write 4 individual delete statements.
Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?
[URL] .....
I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error
View 2 Replies
View Related
Jun 16, 2015
I have a table with about 466 Million rows. In this table there is a int column called WeeksToRetain as well as a EventDate column containing the date the row was inserted. I am trying to delete all the rows that that should be deleted according to the WeeksToRetain. For example, if the EventDate is 5/07/15 with a 1 in the WeeksToRetain column the row should be removed by 5/14/15. I am not sure what days SQL considers the beginning and end of the week. However the core issue I am having is the sheer mass of deletions I must do and log growth.
So I am trying to do the delete in batches. More specifically I want to load a temporary table with a million rows, then use the temporary table to load a sub temporary table with 100,000 rows and join this temporary table to the table I want to delete from looping through 10 times to get 1 million. The Logging.EvenLog table which is the table I'm trying to purge has a clustered index on EventDate (ASC). I would like to run this in a schedule job with enough time between executions for log backups to run.
DECLARE @i int
DECLARE @RowCount int
DECLARE @NextBatchDate datetime
CREATE TABLE #BatchProcess
(
EventDate datetime,
ApplicationID int,
[Code] .....
View 9 Replies
View Related
Aug 4, 2015
I have a table 'library' with two columns
user_name, access_time
every user is having more than 500 entries, i want to keep only top 50 records for every user and delete the remaining records.
View 9 Replies
View Related
Aug 18, 2015
I have a table which has XML data, so it takes lot of time to delete it.
Here's the condition:
Archive all data older than 2 months and delete it.
What would be the good approach for this?
View 3 Replies
View Related
Sep 8, 2015
I have the following scenario:
SQL database on SQL 2012
Large Production table 15 Million record
The table has 3 years of data
New monthly data is being added every month.
A New Monthly data is being loaded, checked and finally approved after 6 or 7 iteration before approval.Because of this iteration the monthly data set is being added then deleted then added then deleted few times.Because the table is big this process takes time, any thoughts on how to make the delete insert process faster.Keep in mind I cannot do much because it is a production table and is being access by other users to do other analysis.
Delete is done based on trx_date which is a year/month combo, like 201508.
The table has monthly sales by customer aggregated.
The table structure is:
CREATE TABLE [dbo].[Sales](
[batch_key] [int] NOT NULL,
[Company_key] [int] NOT NULL,
[customer_key] [char](22) NOT NULL,
[Trx_Date] [int] NOT NULL,
[account] [nvarchar](35) NOT NULL,
[code].....
View 9 Replies
View Related
Feb 21, 2014
We build up a new website in .net mvc 4.0 on a w2012 server with MSSQL 2012 database.We use windows autentification and normal anonymous access through NT AUTHORITYIUSR who is also a login in the database.But we make extra login possibility for users who are registered and they can insert and modify data in some tables in the database. And I'am afraid of giving NT AUTHORITYIUSR insert and update for some column in tables.I think of set up a new login for the database. Run a new connection string on login and set up special permission for that extra user on the database, and let the user become a member of the ordinary user on the database.
View 0 Replies
View Related
Jan 24, 2015
I have created a new login in principle server and provided dbowner permission to principle db.
How do I transfer this login to mirror server and assign the same permission to mirror dd?
View 5 Replies
View Related
May 20, 2015
I have this job that runs and Kills blocking sessions that are longer than 10 mins. I need to add a list of login's that are exclulded from this Job. I cannot seem to get it to exclude specific users in this script. Below is the script.
SET NOCOUNT ON
-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
[code]...
View 4 Replies
View Related
Mar 19, 2014
create table #sample
(
Name varchar(100),
value int
[code]....
From that I wanted to delete some records based on following condition. randomly select any number of records but sum(value) = 125 and name = xxx
View 2 Replies
View Related
Nov 11, 2014
I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which increased performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:
ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
View 5 Replies
View Related
Dec 10, 2014
I decided to delete the encryption key.After that when I go to the reporting page, the report cannot connect to the datasource.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'xxxxx'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
What do I suppose to do?
View 0 Replies
View Related
Feb 24, 2015
I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
View 2 Replies
View Related
