This error is pretty self explanatory. If you are here, I’m sure you simply want to know how to correct it, so here goes. The user context in which you are executing does not have the rights to execute the procedure or function. So first, in order to find out which user you are, simply execute the following query from the connection that is failing. [cc lang=”sql”] SELECT CURRENT_USER [/cc] Now that you know this, you need to login as a user that has the rights to change the execute permissions. Though it is not likely this same user that cannot execute the procedure also has the rights to change the permissions, you can still try. Otherwise simply login as a user with ‘dbo’ access and execute this: [cc lang=”sql”] GRANT EXECUTE ON [dbo].[procname] TO [youruser] [/cc] If you see this error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Then you are out of luck. You are using SQL 2005+ and need to login as a different user.
Continue reading ...
This error message is relatively straight forward. The way it normally happens is when you are trying to insert data from a table that contains values that have larger data lengths than the table you are trying to insert into. An example of this would be trying to insert data from a permanent table, into a temporary table that has data types defined at a fixed length. In order to illustrate the example, we’ll create two temporary tables below and try to insert a value that will not fit into the second temporary table. [cc lang=”sql”] — First create the table that will hold the initial value CREATE TABLE #Employee ( ID int IDENTITY(1,1) ,FirstName varchar(50) ,LastName varchar(50) ) — Insert the example value INSERT INTO #Employee ( FirstName ,LastName ) SELECT FirstName = ‘Billie’ — 6 characters ,LastName = ‘Jean’ — Create the smaller table that will not fit the value CREATE TABLE #Employee_Truncate ( ID int ,FirstName varchar(5) — 5 characters ,LastName varchar(49) ) — Attempt to fit the smaller value into the table INSERT INTO #Employee_Truncate ( FirstName ,LastName ) SELECT FirstName ,LastName FROM #Employee [/cc] From this we see the following error: In order to fix the error, either the second temp table needs to have its data type expanded to be able to fit the data, or you could use the SELECT..INTO method of inserting the data. That way, no matter what size of data the source table changed to, the destination table would automatically […]
Continue reading ...
This error occurs when trying to insert into a column containing an identity. An Identity column is not able to be inserted into without the use of a special command mentioned below. Identity columns are columns that automatically increment when a value is inserted into a row. They are commonly used as primary keys because they guarantee uniqueness. In order to insert into a table containing an identity column SET IDENTITY INSERT ‘tablename’ ON IDENTITY INSERT ON can only be executed by a user having dbo privilidges The following example illustrates the error and shows how to successfully insert. [cc lang=”sql”] — Create MyNames Table with Identity Column CREATE TABLE dbo.MyNames ( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, FName varchar(50) NULL ) INSERT INTO dbo.MyNames ( FName ) SELECT ‘Abe’ UNION SELECT ‘Henry’ UNION SELECT ‘Phil’ — Create YourNames Table with Identity Column CREATE TABLE dbo.YourNames ( ID int IDENTITY(4,1) NOT NULL PRIMARY KEY, –Start Incrementing at 4 FName varchar(50) NULL ) INSERT INTO dbo.YourNames ( FName ) SELECT ‘Bill’ UNION SELECT ‘Candy’ UNION SELECT ‘Sara’ — Attempt to Insert MyNames Identity Into YourNames INSERT INTO dbo.YourNames ( ID, FName ) SELECT ID, Fname FROM dbo.MyNames — we get the error message: Cannot insert explicit value for identity column in table ‘YourNames’ when IDENTITY_INSERT is set to OFF. — Attempt to Insert after turning on IDENTITY_INSERT ON SET IDENTITY_INSERT dbo.YourNames ON INSERT INTO dbo.YourNames ( ID, FName ) SELECT ID, Fname FROM dbo.MyNames SET IDENTITY_INSERT dbo.YourNames OFF [/cc]
Continue reading ...
The first step in diagnosing SQL Server performance is to determine the physical bottleneck with the most contention. Contention in one of the following areas does not always mean that subsystem is performing poorly. It could just as well be improper utilization due to poor tuning. Nevertheless, identifying the bottleneck is always the first place to start. Luckily, Microsoft does provide some unsupported but very useful tools to help us find this. For these, see the video Using SQLDiag and SQL Nexus. I still recommend reading the article below, as it will help understand the interrelation between subsystems. There are ten main bottlenecks that can cause SQL Server to slow down. They are all interrelated and often fixing one causes another bottleneck to be revealed. CPU Memory Disk Paging Blocking Network IO Process Trimming Old Statistics Fragmentation Recompilation Blocking Blocking is caused by contention of resources. To understand blocking you need to understand locking. Locking occurs in order to ensure users see up-to-date accurate information. If records being updated are also being shown to a user before the update finishes, then inaccurate information is being displayed. The most common reason for blocking is the lack of indexing or queries not written to utilize existing indexes. They mostly occur when update or delete statements are performing a table scan, while an incompatible lock like a select statement tries to read the same records. This script will Show the blocking process This script will show the table involved in the blocking And […]
Continue reading ...
The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness. Memory Object: – Memory Counter: – Available Mbytes Preferred Value: – > 2000MB Description: With 64 bit OS’s the available bytes recommendation has increased. Object: – Memory Counter: – Pages/Sec Preferred Value: – < 50 Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system. Object: - Paging File Counter: - %Usage Preferred Value: - < 5% Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot. Object: - Paging File Counter: - %Usage Peak Preferred Value: - < 15% Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot. Object: - SQL Server:Buffer Manager Counter: - Page Life Expectancy Preferred Value: - > 300 Description: – The single […]
Continue reading ...
