SQL Server Admin 2014 :: INSERT Failure After DDL Trigger Creation

Jan 6, 2014

We have created a DDL trigger on SQL server 2005 database for DB audit purpose. Following is the script used for trigger creation

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChangeLog](

[Code] ....

After the DDL trigger creation. Application team started reporting following error while executing a stored procedure.

*********************************
Error 1:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Error2:
[Execute SQL Task] Error: Executing the query "exec sp_drop_indexes_EnhLeaseData delete from dbo.leases where vin_num='XXX' and lease_acct_num='XXXX' delete from dbo.leases where vin_num='XXX' and lease_acct_num='080066225' " failed with the following error: "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 1 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Master Table With Insert Trigger

Feb 4, 2015

I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..

Is there anyway to capture the record either in the master table or in a separate table.

View 6 Replies View Related

SQL Server Admin 2014 :: Difference In Performance Counter Names In Dmv And Alert Creation Screen

Feb 13, 2015

SQL Server 2014 BI edition.

select * from sys.dm_os_performance_counters returns the object names prefixed with "SQLServer:" (e.g. SQLServer:Databases)

It was expected as in other editions also. Issue is that when we try to crate "SQLServer Performance Condition alert", object names in "Object" list comes without the prefix "SQLServer:" (e.g. Databases). Please see the attached snapshots.

View 4 Replies View Related

SQL Server Admin 2014 :: Configuring Email On Replication Failure

Jun 10, 2015

We have push transaction replication from A database to 2 other B and C database. I have configured email to sent upon failure of subscription job of B and D database on Database A. Is this the way that we need to configure email to send when there is replication break up or failure.

Database is MS sql server 2008 R2
Publication Database: A
Replication mode : Transaction Replication
Replication used : Push Replication from database A
Subscription Database: B and C

View 0 Replies View Related

SQL Server Admin 2014 :: Restore Database Permission Failure

Jul 29, 2015

I'm trying to figure out why this SQL Agent job keeps failing.

We used management studio, and connected as mydomainmyuser, and developed a script to take a backup file from a network share and restore it. It worked fine in SSMS under that login.

After we got it working , we created a SQL Agent job on the same server to run the script, and set the agent job to run under that account that we tested with.

This is the error message we got:

"Executed as user: mydomainmyuser. Create Database permission denied in database master'.

So, I gave that login the rights to Create Database and Create Any Database. Then the error message changed to:

"Executed as user: mydomainmyuser. User does not have permission to RESTORE database 'mydatabase'. [SQLSTATE 42000][ERROR 3013] RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "

* I can't use SA for the job account, because the SA account doesn't have rights to see the network folder where the backup file sits, so it has to run under the domain account.
* The user is a member of the dbcreator role - and the serveradmin and sysadmin roles
* The user is a member of dbowner on the database I am trying to overwrite with the restore
* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE

The only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.

I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the ServerInstanceName is MyServer-New

[URL] .....

SELECT HOST_NAME() AS 'host_name()',
@@servername AS 'ServerNameInstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'

I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.

Am I on the right track with the name change messing up permissions, or is there something else I need to check?

View 5 Replies View Related

SQL Server Admin 2014 :: Send Email On Scheduler Job On Both Failure And Success

Jun 10, 2015

I have configured smtp email in MS sql server and configure email to schedular job when schedular jobs become failed. Can i configure email so that email will be sent from scheduler job on both success of job and Failure of job?

View 3 Replies View Related

SQL Server Admin 2014 :: Using Machine Name Already Exists Error After Cluster Installation Failure

Sep 10, 2015

Due to a SQL 2014 cluster installation failure related to security while setting up the primary node, I had to remove the node and any related installation programs from the node and redo the installation again. However I am unable to use the machine name as the SQL network name during the instance configuration, I get the following:

Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException: The SQL Server failover cluster instance name "MachineName" already exists as a server on the network. Specify a different failover cluster instance name.I am still getting the same name since the node name "MachineName" is listed under the cluster name. I have used machine name as SQL network name without an issue. I do not have any existing SQL machine name in network using same machinename which I want to use for this installation.

View 9 Replies View Related

SQL Server Admin 2014 :: SSAS Cube Processing Fail With Communication Link Failure Error

Feb 4, 2015

we have our cubes in Server A and SQL DB resides on Server B (we are on SQL 2014), from last couple of days are cube started failing due to below error:

OLE DB or ODBC error: Protocol error in TDS stream; HY000; Protocol error in TDS stream; HY000; Protocol error in TDS stream; HY000; Communication link failure; 08S01; TCP Provider: An existing connection was forcibly closed by the remote host.
; 08S01

I have been going through some blogs to understand the error but don't find any specific yet.

View 0 Replies View Related

SQL Server Admin 2014 :: Server Level Trigger To Log Activity And Rollback After Logging Information

Sep 8, 2015

I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:

create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end

I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.

View 8 Replies View Related

SQL Server Admin 2014 :: Logon Trigger Executing Multiple Times For Single Connection?

Jan 30, 2015

I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.

This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?

I also tried using the After Logon option, and got the same issue.

Here is the code:

CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),

[code]....

View 0 Replies View Related

SQL Server Admin 2014 :: Why Number Of Reads Increases During Insert Test

Jun 2, 2014

I am writing a performance baseline test.

The first test writes 5000000 rows in one table. I realise this is not representative OLTP behaviour, but it worked me to start interpreting performance counters and to test several setups to be discussed with our server, storage and network administrators. This way we have been able to compare the results of different hard disks, Lun vs vmdk, 1GB vs 10GB network, AMD vs Intel, etc. This way I can also compare several SQL setups (recovery model, max memory config, ...)

The screenshot shows the results of 2 runs on the same server : Win2012R2, SQL2014, 16GB RAM.

In test 1 min/max server memory was set to 9215MB/10751MB
In test 2 min/max server memory was set to 13311MB/14847MB

The script assures the number of bytes inserted in the nvarchar columns is always the same.

This explains why the number of pages and the number of MB in the table are the same at the end of the 2 tests (column 5 and 6)

Since ca 13GB has to be written, the results of test 1 show the lead time is increasing once more than 10GB has been inserted (column 8 and 9) In addition you can see at that moment

- buffer cache hit ratio is decreasing
- page life expectance becomes "terrible"
- free list stall/sec increases
- lazy writes/sec increases
- readlatency increases (write latency does not)

In test 2 (id 3 in column 1 in the screenshot) those counters are not really influenced (since the 5000000 rows can all be stored in memory).

Now what I do not understand is :

Why the number of pages read (instance level) as well as the number of bytes read and the number of reads (databaselevel) is increasing extremely during run 1.

I expected to see serious impact on write behavior, since SQL server is forced to start flushing dirty pages once memory is filled. Well actually you can see here the number of writes (not the the number of bytes written) starts to increase faster in test 1 after 4000000 rows, but there's no real impact on write latency.

Finally I want to notice

- I'm the only user on this machine
- the table has a clustered index on a identity column
- there are no foreign key constraints
- inserts are executed using a loop, not one big transaction
- to monitor progress and behaviour/impact, each 10.000 loops the counters are stored using dmv queries

So I wonder why SQL Server starts to execute so many reads in test 1.

View 4 Replies View Related

SQL Server 2014 :: Trigger On A View If Any Insert / Update Occurs On Base Table Level

Apr 21, 2015

I have a situation where I have Table A, Table B.

View C is created by joining table A and table B.

I have written a instead of trigger D on view C.

I do not insert/update/delete on the view directly.

For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.

And I am unable to see this trigger work on the view if any insert/update occurs on base table level.

Trigger is working only if any operation is done directly on the view.

View 2 Replies View Related

SQL Server Admin 2014 :: Insert A Row To A Table Based On Table Values?

Jun 10, 2015

Here is my table:

My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?

View 0 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

SQL Server 2014 :: Programmatic Creation Or Modification Of SSIS

May 21, 2015

I'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.

I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.

What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?

View 2 Replies View Related

SQL Server Admin 2014 :: Encryption Key Not Known

Apr 14, 2015

I inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.

The encryption Key was never backed up and it seems that no one knows what the password is?

Do I have to manually load the reports? There are a lot of Reports.

[URL]

View 4 Replies View Related

SQL Server Admin 2014 :: BCP Aborts On First FK Violation

Sep 26, 2013

I want to use BCP to load data from a text file.

By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.

bcp aborts if ANY of the rows contains a FK violation. No data get loaded.

So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.

I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?

View 2 Replies View Related

SQL Server Admin 2014 :: What Is The Default SA Password

Jan 13, 2014

If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?

View 9 Replies View Related

SQL Server Admin 2014 :: How To Restore MDF File

Mar 21, 2014

My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.

View 9 Replies View Related

SQL Server Admin 2014 :: Return One Field / One Row

Jun 18, 2014

I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.

View 2 Replies View Related

SQL Server Admin 2014 :: DNS Pointing To A Listener

Jun 25, 2014

In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?

View 1 Replies View Related

SQL Server Admin 2014 :: How To Schedule A Job To Run At Different Intervals

Jul 31, 2014

Is there a way to schedule a sql job to run at different intervals

For eg:
The job should run at
7:00 Am
8:00 AM
and then at 10:00 Am

View 3 Replies View Related

SQL Server Admin 2014 :: Non-yielding On Scheduler

Nov 19, 2014

We installed the Sql 2014 in Test server.

We observed frequently

"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."

Is it better to run the profiler or performan counter?

What are the filters we have to select in the profiler to monitor the Sql server

View 0 Replies View Related

SQL Server Admin 2014 :: Reporting Services Through IIS?

Dec 8, 2014

I have a SQL server box running 2014 reporting services. I have another server running IIS v8.

I would like to be able to connect to the IIS site and be given the SSRS report browser.

So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.

What are my options?

View 4 Replies View Related

SQL Server Admin 2014 :: DB Read Only Copy

Dec 11, 2014

What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.

The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.

The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.

What would best the approach to accomplish the end result?

View 3 Replies View Related

SQL Server Admin 2014 :: Possible Errors In AlwaysOn

Jan 12, 2015

I did not worked on AVG in sql 2012, what are the possible errors and how to resolve in always on in sql 2012 .

View 1 Replies View Related

SQL Server Admin 2014 :: Script To Failover

Jan 18, 2015

I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?

View 3 Replies View Related

SQL Server Admin 2014 :: Delay In Between Replicas In AG

Feb 18, 2015

How to check the delay in between replicas in AG?

View 3 Replies View Related

SQL Server Admin 2014 :: OST File Is Not Accessible

Feb 26, 2015

my 4 GB .OST file is corrupted and i am not able to access my mails and contacts.

View 2 Replies View Related

SQL Server Admin 2014 :: How To Identify The Job With The Code

Apr 7, 2015

I got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1

Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61

View 1 Replies View Related

SQL Server Admin 2014 :: SSMS Will Only Run As Administrator

May 28, 2015

After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.

View 3 Replies View Related

SQL Server Admin 2014 :: Old Backups Will Not Delete

Jun 19, 2015

Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.

View 9 Replies View Related

SQL Server Admin 2014 :: What Does Restore Log Do After The Log Is Restored

Jul 29, 2015

when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.

Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.

What does SQL Server or Windows do after the restoring?

pic a:

pic b:

I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .

pic 1:

pic 2:

see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?

Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved