:::: MENU ::::

Posts Categorized / SQL Server Error messages

  • Aug 23 / 2013
  • 0
dbDigger, PowerShell, SQL Server Error messages, System Administration

File %.ps1 cannot be loaded because the execution of scripts is disabled on this system.

Now a days i am working on a powershell task. This morning i prepared a basic powershell script and run it to see that how close are the results. I called the powershell script from cmd so that any errors may be seen if generated. To my surprise it generated a different type of error pointing to some configuration

Error while executing power shell script

In a more readable text form the errror statement is

File %.ps1 cannot be loaded because the execution of scripts¬† is disabled on this system. Please see “get-help about_signing” for more details.

I looked for the solution and implemented it to allow PS scripts run on my stystem. Following is the brief solution that i implemented.

Step1: Check current system setting for scripts

launch powershell and execute Get-ExecutionPolicy 

http://2.bp.blogspot.com/-uucE5IKdRiQ/UhdOv5PTtpI/AAAAAAAADSU/mVI7kpc2HHg/s1600/Get+Policy+settings+for+scripts+execution

It shows that current script execution is restricted.

Step2: Change the script execution policy

Now we have to change the script execution policy. We have three other options to set.

  • RemoteSigned: You can run your own scripts but downloaded scripts will have to be signed in order to run on the system.
  • AllSigned: Your own or dowloaded scripts all should be signed to run.
  • Unrestricted: Remote or your own scripts may be run without signing check.

So you may choose an appropriate option and run one of the following command according to choice

  • Set-ExecutionPolicy RemoteSigned
  • Set-ExecutionPolicy AllSigned
  • Set-ExecutionPolicy UnRestricted

Step 3: Verify

You may verify the new policy implementation by repeating step 1. This time you will get the new policy name instead of restricted policy.

After implementing this solution i proceeded with my original tasks by writing and executing the PS scripts successfully. Hope same for you.

  • Aug 21 / 2013
  • 0
dbDigger, DDL, DDL Triggers, SQL Server Error messages, SQL Server Integration Services (SSIS), Triggers

Error generated in SSIS task

While importing few tables from server instance to another i got following error message.

http://4.bp.blogspot.com/-SQO5uAIPeh8/UhTljUKxEWI/AAAAAAAADR4/Xm_GZfJcKfQ/s1600/Problem+with+the+query+Result+Set+property+not+set+correctly,+parameters+not+set+correctly+or+connection+not+established+correctly

The user does not have permission to perform this action. Possible failure reasons: Problem with the query. “Result Set” property not set correctly, parameters not set correctly or connection not established correctly.

Error message was not looking fit for my scenario. In the mean while i got another report about errors being generated while creating tables on same server. The error message was pointing to failure of a DDL trigger meant for logging purpose. The trigger was being invoked on table creation and got failed due to permission issues while inserting logs in trigger log table. So i disabled the trigger and executed the task again. This time task was completed successfully.

So if you fall in above mentioned scenario then along with checking the reasons provided in error message also look for DDL trigger context.

  • Dec 13 / 2012
  • 0
dbDigger, Disaster Recovery, Maintenance plans, SQL Server Agent scheduled Jobs, SQL Server Error messages, Transaction log files

Log backups fail after changing the SIMPLE recovery model

Recently a scenario was shared with me where maintenance plan was failed to create the log backups. Actually recovery model of DB was set to SIMPLE to prevent the log file population for some log intensive bulk operations. After completing the operations recovery model was put back to FULL. Every thing was OK till this point but maintenance plan job began to failed later for creating log backups of that database with following error

BACKUP LOG cannot be performed because there is no current database backup.

The reason for error is that after changing the recovery model of DB from SIMPLE to BULK LOGGED or FULL, we have to create a full or differential backup before going for log backup.
Solution to avoid such error is simple that we have to go in following sequence

  • Change recovery model to simple
  • Complete the required operations
  • Change back to FULL or BULK LOGGED recovery model
  • Create FULL or DIFFERENTIAL backup
  • Successfully create log backups
  • Here is a quick demo to simulate the above steps

    USE [master]  
     GO  
     -- Set the DB to simple recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY SIMPLE  
     GO   
     -- Set the DB to full recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY FULL  
     GO   
     -- Try to Backup the log, it would fail with error  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
     -- Now Create full or differential backup of database  
     Backup DATABASE DBDIgger   
     to Disk = 'C:DBD-FullBackup.bak'   
     GO  
     -- Try to Backup the log, it would be OK  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
    
    • Feb 29 / 2012
    • 0
    Data Modeling and Database Design, Data Types, dbDigger, DDL, SQL Server Error messages

    Table creation failed due to row size limit

    Following error messages may be generated while trying to create index on table having row size greater than 8060 bytes. In case where index is also tried to create following error would be generated
    Index ‘%’ row length exceeds the maximum permissible length of ‘8060’ bytes.

    And while trying to create table with out index and size greater than 8060 bytes, following error would be generated
    Creating or altering table ‘%’ failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

    SQL Server extent size is 8060 bytes and is comprised of 8 pages. Individual table row under 8060 bytes size is requirement of table creation. However data types like image or text would not be considered as participant in max size. If row size is expected to cross this limit then error message would be generated and creation would be failed.

    Work Around

    Some work around may be applied to solve the issue.

    • Try to cut down the number of columns by normalization
    • Try to optimize the data types
    • Study feasibility to change columns like char(8000) columns with text data type

    Following are some scenarios where error messages related to row max size may be generated

    -- Row size error message without index   
     IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
     DROP TABLE tblMaxRowDemo  
     GO  
     CREATE TABLE tblMaxRowDemo  
     (ID INT IDENTITY(1,1),  
     col1 CHAR(6000),col2 CHAR(6000))  
     GO  
     -- Row size error message with index   
     IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
     DROP TABLE tblMaxRowDemo  
     GO  
     CREATE TABLE tblMaxRowDemo  
     (ID INT PRIMARY KEY IDENTITY(1,1),  
     col1 CHAR(6000),col2 CHAR(6000))  
     GO  
    
    • Feb 24 / 2012
    • 0
    Backup and Restore, dbDigger, SQL Server Error messages, SQL Server IO, System Administration, Windows Server

    Database backup and restore failed on mapped network drive

    During my job at National Database and Registration Authority of Pakistan (NADRA), i was accustomed to create database backups directly on mapped network drives. We have a domain configured at NSRCs with SQL Server 2000. Fore servers with single disk, it becomes very efficient by preventing the local server disk IO. Now while working with SQL Server 2005 installed on windows server 2003 work group. i was required to create an adhoc backup during peak hours. For optimized IO i tried to use mapped network drive for creating backup on it directly. But surprisingly mapped network drive was neither available in SSMS backup location explorer nor through T-SQL backup statement. Error message was being generated.

    Database backup and restore on mapped network drive

    Same statement was working successfully for any location other than mapped network drive. Keeping in view my previous experience with SQL Server 2000 on domain i was not expecting any problem in this task. However after some time i realized that network drive is not available as backup device.

    Next option was BOL and Google. There i came to know that mapped network drives are available for backups only when your SQL Server service is running under domain account that has access to mapped network drive.

    Conclusion

    Without a privileged domain account as start up account of SQL Server service, mapped network drives cannot be accessed from within SQL Server context for backup or restore process.

    • Feb 22 / 2012
    • 0
    dbDigger, Deployment, SQL Server Error messages, System Administration, Windows Server

    Role management tool to install or configure .Net framework on Windows Server 2008

    While installing SQL Server 2008 on Windows Server 2008 R2, i got following error message at initial stage in process.

    Error message in SQL Server 2008 setup related to Role management tool

    Windows was up to date and required version of .Net framework was installed on it. Opened Server Manager for Windows Server 2008 and clicked on features in left panel. Then by clicking Add Features link in right panel another panel was opened with several features available there for installation. I checked the box for my required .Net framework feature and Clicked on Next button to proceed with the installation.

    Service Manager in Windows Server 2008 for .Net Framework features

    The .Net feature was installed and SQL Server 2008 installation ended successfully.

    Pages:123
    Consult us to explore the Databases. Contact us