:::: MENU ::::

Posts Categorized / SQL Server Error messages

  • Mar 24 / 2014
  • 2
dbDigger, Replication, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint (Replication error)

We had configured transnational replication on a QA server. Publisher and subscriber were on same server. While implementing snapshot we were facing following error message as in replication monitor.

Command attempted:
if object_id(N’sys.sp_MSrestoresavedforeignkeys’, ‘P’) < 0 exec sys.sp_MSrestoresavedforeignkeys
(Transaction sequence number: 0x000B0F2B0002BB5101C300000000, Command ID: 1)

Error messages:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ContractID2”. The conflict occurred in database “%”, table “%”, column ‘%’. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ContractID2”. The conflict occurred in database “%”, table “%”, column ‘%’. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547

Subscriber database had no constraint that was mentioned in the error message. Even publisher did not have this constraint. It was a mess that we were not able to find the constraint but it was causing failure to snapshot implementation.

Towards Solution

While looking into details i came across following there tables that were being created by replication processes and contain the foreign key data of replication.

  • dbo.MSsavedforeignkeys
  • dbo.MSsavedforeignkeycolumns
  • dbo.MSsavedforeignkeyextendedproperties

I noticed that ghost enteries for our culprit foreign key were there in these tables. I deleted those entries manually and regenerated the snapshot. This time it was successfully generated and implemented.

  • Jan 31 / 2014
  • 0
Database Mail, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Last day i was working on a task which was meant to generate some data and send it as an email attachment to a specific email address. This process was going to be configured as a scheduled job in SQL Server agent. I completed the first part of task and data was ready as a CSV file to be send through email.

Problem Phase

In the second phase while trying to send the file as an attachment i got following error message.

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Amount of bytes shows that about 1 MB file size cap was there on attachment file that should be send through DBMail. In my case we were expecting the attachment file of size up to 25 MB.
So the questions are

  • Can we change the default limit on attachment size in database mail .i.e. 1000000 bytes?\
  • If yes then where can we change this parameter?

Solution

Yes we can change the default limit on attachment size in database mail. And related to the procedure of changing this parameter following are the steps

  • Open SSMS and go to Management folder
  • Right click on Database Mail and select Configure Database Mail
  • Click Next and select view or change system parameters radio button
  • Click next and you will be presented the parameters screen where you can view and modify the parameters as well
  • Here you can change the value against the Maximum File Size (Bytes) parameter
  • Specify the required size in bytes and click Next to save the configurations

Now you can send attachment up to mentioned max size here without any error.

What is the limit of Max value?

It is a valid question here at this point that what is the limit of value that we can enter as maximum file size in bytes? The answer is 2147483646.
Yes this parameter is of type int and can accept value under 2^31-1 (2,147,483,647) bytes. This mean that roughly you can get about 2GB size against this parameter. If you enter exceeded value for size then following error message will be generated

Account Retry Attempts, Account Retry Delay, Maximum File Size, Database Mail Executable Minimum Lifetime, and Maximum Number of Mails must have valid values to continue.

Value was either too large or too small for an Int32. (mscorlib)

  • Dec 06 / 2013
  • 0
Backup and Restore, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages, Xp_CmdShell

Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’

Recently i got an job failure email alert. This job is involved in backup of few databases and as a part of process it was required to create directories for each database as well. When i explored the job history to get the failure reason following message was found in log

Message
Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’ [SQLSTATE 42000] (Error 22048). The step failed.

sa is owner of this job and there is no apparent role of NT AUTHORITYSYSTEM as mentioned in the message. However there is a by design flow that involved NT AUTHORITYSYSTEM.
Point to note is that SQL Server service was configured to run under local system account.

Reason:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. In this case xp_cmdshell was being invoked by sa i.e. sysadmin so it is being executed in context of local system account. Local account was NT AUTHORITYSYSTEM that has no access to create the directories on backup location.

Solution:

To solve the issue i changed the SQL Server service login to a valid domain account that has access to create folders and files on network location for backup.

  • Dec 03 / 2013
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, DDL, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Today i was working to create foreign keys on few tables. Some of ADD CONSTRAINT statements got failed by generating the following error message.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “%”. The conflict occurred in database “%”, table “%”, column ‘%’.

If we look closely at the descriptive message it points to the same foreign key constraint that we are just trying to create. Reason is that while creating the constraint SQL Server is trying to validate the existing data based on new constraint. There are some records with no reference in their base primary key table. Such records are cause of error here.

SOLUTION

we have couple of options. If you want to make sure that no such orphaned records be there in subject table you may just detect and delete them. Then ADD CONSTRAINT statement will work without any error. However deleting the data will hardly be a suitable option. Alternate is to use the ADD CONSTRAINT statement with NOCHECK option. It will make sure that existing data is not validated for constraint at time of creation.

WITH CHECK | WITH NOCHECK

Here is some informative text on these options from BOL.

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

QUERY OPTIMIZER BEHAVIOR

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  • Aug 28 / 2013
  • 0
dbDigger, SET Options, SQL Server Error messages

Error message when creating assembly in a SQL Server database

I was required to transfer SQL Server assembly along with the other objects from one SQL Server instance to another. I used Transfer SQL Server Objects which is a handy control of SSIS. During the test process i get following error as a result of trying to create assembly in target database.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: “ERROR : errorCode=-1073548784 description=Executing the query “CREATE ASSEMBLY [%]
AUTHORIZATION [dbo]
FRO…” failed with the following error: “CREATE ASSEMBLY for assembly ‘%’ failed because assembly ‘%’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}”.

This error is produced because before creating the assembly we have to mark the database as trustworthy. By default trustworthy mode is disabled for SQL Server databases. Trustworthy mode tells the server that this database contains controlled creation of objects and there is no chance that some one will create malicious objects in it or some one will manipulate it by attach/detach process. So if you are satisfied with above assumptions that you are going to give to your server then use following command in subject database and set it as a trustworthy database

ALTER DATABASE [DBNameHere] SET TRUSTWORTHY ON
GO

After this step we are now able to create and refer assemblies in SQL Server database and error exists no more.

  • Aug 27 / 2013
  • 0
dbDigger, PowerShell, SQL Server Agent scheduled Jobs, SQL Server Error messages

Error message while using PowerShell in scheduled job

I was required to call PowerShell script from SQL Server scheduled job. Following was the command to access and run the PS script saved on disk.
powershell -command “& ‘D:PscriptsTransfer.ps1’ ”

This command syntax may be saved in batch file or directly run from cmd. However surprisingly i was facing following error message in job history. It is notable that job status was successful however error messages were logged there in job history.

A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘powershell -command “&’%'”‘. Correct the script and reschedule the job. The error information returned by PowerShell is:

Apparently it was pointing towards syntax used in the command however i did not find any issue in the command. It was working fine outside the SQL Server scheduled job environment. Also i did not get any help through googling. However i analyzed the issue in terms of permissions that we mostly face in scheduled job environment.

So first step was to create a proxy account. I will cover the topic of creating proxy account for SQL Server agent job in a separate post. After creating the proxy account i used it in scheduled job and ran the job to test.
The idea worked and job executed successfully.
Pages:123
Consult us to explore the Databases. Contact us