TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4266&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476
FIX
GRANT EXECUTE ON xp_prop_oledb_provider TO [UserDomain\Account Name];
Wednesday, February 3, 2010
Error when using dtutil to deploy SSIS packages
BIDS Helper is deploying packages...
Deploying to SQL Server MSDB on server: NAMEOFSERVER
Error : BIDS Helper encountered an error when deploying package PACKAGENAME.dtsx!
"c:\Program Files\Microsoft SQL Server\90\DTS\binn\dtutil.exe" /FILE "PATHTOPACKAGE.dtsx" /DestServer NAMEOFSERVER /COPY SQL;"Package" /Q
exit code = 6
Microsoft (R) SQL Server SSIS Package Utilities
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.
Could not save package "PACKAGE" because of error 0x80004005.
Description: Cannot open database "msdb" requested by the login. The login failed.
Source: Microsoft SQL Native Client
Had to give the user's account access to msdb and the dts_admin and dts_operator roles.
Monday, January 25, 2010
SSMS short cuts
Although I have been using SSMS for a number of years now I didn't realise you could create your own short-cuts until I saw it mentioned on the UK SQL Server User Group: http://sqlserverfaq.com/
SSMS allows you to create your own short cut keys:
Under the Menu Tools Select Options
Under Environment >> Keyboard there is a list of shortcut commands.
A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;
I have added:
Ctrl+F1: master..xp_fixedDrives; Shows how much space is available on the fixed drives
Ctrl+3: Select * from sys.databases; Lists all databases
Ctrl+4: SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB' FROM sys.database_files; Shows how much space is available in SQL Data files
Ctrl + 5: DBCC SQLPERF(logspace); shows log files, the size of the file and % free space.
I have since added a couple more. One shows server waits and another shows server activity in a bit more details than sp_who2. I think I'll keep these as floating short cuts and change them depending on what I am working on or investigating.
SSMS allows you to create your own short cut keys:
Under the Menu Tools Select Options
Under Environment >> Keyboard there is a list of shortcut commands.
A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;
I have added:
Ctrl+F1: master..xp_fixedDrives; Shows how much space is available on the fixed drives
Ctrl+3: Select * from sys.databases; Lists all databases
Ctrl+4: SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB' FROM sys.database_files; Shows how much space is available in SQL Data files
Ctrl + 5: DBCC SQLPERF(logspace); shows log files, the size of the file and % free space.
I have since added a couple more. One shows server waits and another shows server activity in a bit more details than sp_who2. I think I'll keep these as floating short cuts and change them depending on what I am working on or investigating.
Thursday, November 19, 2009
Reporting Services Email Subscriptions SSRS 2005
This assumes a user does not have Content Manager as their SSRS role and SSRS is set up more or less as it comes out of the box.
The Problem
If a user hits subscribe on a report then the chooses email as the delivery option they will see that the To field has been pre-populated with their windows login. They will not have the permissions need to change this setting. This is fine if you are running Exchange as it will resolve the login against the Active Directory and work out the real email address.
If you are not running Exchange it is not so fine.
The fix
You need to make a change to the rsreportserver.config file.
This should be under C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer by default – if you installed SQL somewhere else then the first part of this path will be wrong.
Open up this file and look for a value <sendemailtouseralias> under <rsemaildpconfiguration>. If this is set to True, as it is by default, you will get the problem above. If it is set to False the To field will be blank and the user can enter an email address.
Sunday, November 8, 2009
An error occurred while executing batch
I ran a query which resulted in this:
An error occurred while executing batch. Error message is: The directory name is invalid.
The reason for this was an invalid temp directory. The system was pointing at c:\temp but the directory did not exist. I re-pointed all the temp and tmp environment variables at the real temp directory and all was well again.
An error occurred while executing batch. Error message is: The directory name is invalid.
The reason for this was an invalid temp directory. The system was pointing at c:\temp but the directory did not exist. I re-pointed all the temp and tmp environment variables at the real temp directory and all was well again.
Monday, November 2, 2009
MCITP: Database Administrator.
I passed the 70-444 exam today which is the final exam in the MCITP: Database Administrator trilogy.
This exam was 52 multiple choice questions - old style mcp type exam.
The 70-443 was a set of 6 case studies with 12 questions on each.
I find that after revising for a while I begin to get what they are testing and can normally answer the question based on the answer options - although reading the question helps!
For the 70-443 exam I skimmed through the case studies and then went back to look at the specifics as they related to the individual questions.
I have still got to finish off the 2000 MCDBA. I have the 2 SQL exams just need to do the windows admin exams.
This exam was 52 multiple choice questions - old style mcp type exam.
The 70-443 was a set of 6 case studies with 12 questions on each.
I find that after revising for a while I begin to get what they are testing and can normally answer the question based on the answer options - although reading the question helps!
For the 70-443 exam I skimmed through the case studies and then went back to look at the specifics as they related to the individual questions.
I have still got to finish off the 2000 MCDBA. I have the 2 SQL exams just need to do the windows admin exams.
Tuesday, October 27, 2009
Step by Step guide to log shipping on SQL Server 2005
What it is and how it works.
Log shipping is the process of transferring transaction logs from one server to another – shipping them. It is used as a form of disaster recovery for a database. It does not have any automated fail over – transfer to the new database will involve human intervention.
Two servers are involved, the Primary server and the Secondary Server. The Primary server is the server with the database that is being protected. The secondary server holds the backup copy of the database.
The database to be protected needs to be in Full or Bulk Logged recovery mode. This is to stop the transaction logs being truncated. A full backup should then be taken and applied to the secondary server. The option No Recovery is used when restoring on the secondary server. This leaves the database in a restoring state. No log shipping is set up and transaction log backups are copied from the primary server to the secondary server and then applied to the restoring database. This has the affect of keeping the database in step with the primary version.
As an extra process login accounts need to be transferred to the secondary server. This way if the database ever has to be brought on line and used access will be the same as it is on the primary server.
How to set up Log Shipping
In this step by step guide I will set up log shipping on the DataImports database from serverA to serverB.
Create a directory on the primary server
On the primary server create a directory for the transaction log backups. The backups will be made into this directory and then copied across to a receiving directory on the secondary server.
I create this under the main maintenance or sql backup directories.
If there is not one there already create a folder called LS. Then create a sub-folder with the name of your database: \\serverA\m$\Backups\LS\dataImports
Create a directory on the secondary server
On the secondary server create a shared directory for receiving the log shipped files.
Again I create this main maintenance or sql backup directories.
If there is not one there already create a folder called LS. Then create a sub-folder with the name of your database: file://serverb/f$/Maint/LS/dataImports
Exclude the database from TLog backups
If you have any maintenance plans which carryout Transaction Log backups for this database you will need to exclude it.
Set recovery model of the database to Full
If the recovery model of the database is simple set it to Full. You can leave it if it is set to Bulk Insert.
Backup the database
Take a full backup of the database in to the folder you created on the primary server and copy it to the new folder on the secondary server.
Create a database on the secondary server and then restore the backup
Create a new database with the same name as the one you are going to log ship.
Now restore the database using the backup you have just taken – make sure you set the file paths to restore over the files you have just created and not try to use the paths the backup sets.
You must select the option to restore with no recovery.
You can also delete the backup file you used for the restore.
Configure Log Shipping
In SSMS right click on the database name and select properties.
Click on Transaction Log Shipping. Now tick the box which says “Enable this as a primary database in a log shipping configuration”
The button ‘Backup Settings’ will now be enabled. Click this and fill out the options.
You should enter the full path to the folder on the primary server. Underneath this you should also enter the local path. In my example the dataimports data rarely changes so I have set different values from the default. I am deleting files older than 24 hours – 72 is the default. I am alerting on 2 hours where 1 is the default. Next click on the schedule button. As the database I am using does not change very often I have scheduled my backups to take place once an hour. The default is every 15 minutes.
Now you are back on the secondary database settings make sure the option ‘no, the secondary database is initialised’ has been selected. Now click the Copy Files tab. In this screen you need to enter the path for the directory on the secondary server: file://serverb/f$/Maint/LS/dataImports
I change the deleted copied files option to 24 hours again. Now click schedule. I change this to run every hour again – the default is 15 minutes. This time I will also change the start time to 00:15. There is no point it trying to copy the files on the hour as the actual backup is being taken on the hour. Click Ok and that task will be scheduled.
Now click on the Restore Transaction Job task. I change the delay option to 5 minutes and the alert option to 2 hours. This is because I have a slow rate of change and so only copy every hour. Click on schedule again. This time I will set the task to run at 30 minutes past the hour and to run hourly. Click ok and then on the next screen click on again.
You are now on the properties page again.
Click ok again and the server will create the log shipping jobs. Once the two items have gone green you can click close.
Run the Log Shipping Jobs.
On the primary server there will be a job named like LSBackup_DatabaseName.
Run the LSBackup_DatabaseName job and check that a trn backup file has appeared in the folder you created on the primary server.
On the secondary server look for a job like LSCopy_PrimaryServerName_DatabaseName and run this job. You should see the trn file appear in th efoledr you created on ServerB. Now run LSRestore_PrimaryServerName_DatabaseName.
Finally Transfer Logins
You should use SSIS to create a transfer logins task to copy the logins from serverA to serverB. This way if you have to fail the database over to serverB you can enable to logins and won't have any permissions problems.
Log shipping is the process of transferring transaction logs from one server to another – shipping them. It is used as a form of disaster recovery for a database. It does not have any automated fail over – transfer to the new database will involve human intervention.
Two servers are involved, the Primary server and the Secondary Server. The Primary server is the server with the database that is being protected. The secondary server holds the backup copy of the database.
The database to be protected needs to be in Full or Bulk Logged recovery mode. This is to stop the transaction logs being truncated. A full backup should then be taken and applied to the secondary server. The option No Recovery is used when restoring on the secondary server. This leaves the database in a restoring state. No log shipping is set up and transaction log backups are copied from the primary server to the secondary server and then applied to the restoring database. This has the affect of keeping the database in step with the primary version.
As an extra process login accounts need to be transferred to the secondary server. This way if the database ever has to be brought on line and used access will be the same as it is on the primary server.
How to set up Log Shipping
In this step by step guide I will set up log shipping on the DataImports database from serverA to serverB.
Create a directory on the primary server
On the primary server create a directory for the transaction log backups. The backups will be made into this directory and then copied across to a receiving directory on the secondary server.
I create this under the main maintenance or sql backup directories.
If there is not one there already create a folder called LS. Then create a sub-folder with the name of your database: \\serverA\m$\Backups\LS\dataImports
Create a directory on the secondary server
On the secondary server create a shared directory for receiving the log shipped files.
Again I create this main maintenance or sql backup directories.
If there is not one there already create a folder called LS. Then create a sub-folder with the name of your database: file://serverb/f$/Maint/LS/dataImports
Exclude the database from TLog backups
If you have any maintenance plans which carryout Transaction Log backups for this database you will need to exclude it.
Set recovery model of the database to Full
If the recovery model of the database is simple set it to Full. You can leave it if it is set to Bulk Insert.
Backup the database
Take a full backup of the database in to the folder you created on the primary server and copy it to the new folder on the secondary server.
Create a database on the secondary server and then restore the backup
Create a new database with the same name as the one you are going to log ship.
Now restore the database using the backup you have just taken – make sure you set the file paths to restore over the files you have just created and not try to use the paths the backup sets.
You must select the option to restore with no recovery.
Click OK.
The database name will now have a green arrow next to it and say restoring …You can also delete the backup file you used for the restore.
Configure Log Shipping
In SSMS right click on the database name and select properties.
Click on Transaction Log Shipping. Now tick the box which says “Enable this as a primary database in a log shipping configuration”
The button ‘Backup Settings’ will now be enabled. Click this and fill out the options.
You should enter the full path to the folder on the primary server. Underneath this you should also enter the local path. In my example the dataimports data rarely changes so I have set different values from the default. I am deleting files older than 24 hours – 72 is the default. I am alerting on 2 hours where 1 is the default. Next click on the schedule button. As the database I am using does not change very often I have scheduled my backups to take place once an hour. The default is every 15 minutes.
Click OK.
You are now on the configuration screen again. There is a white box where it says secondary server instances and databases. Underneath the box click add. Click on the button at the top right which says connect. Enter the secondary server details. The database should be selected by default.Now you are back on the secondary database settings make sure the option ‘no, the secondary database is initialised’ has been selected. Now click the Copy Files tab. In this screen you need to enter the path for the directory on the secondary server: file://serverb/f$/Maint/LS/dataImports
I change the deleted copied files option to 24 hours again. Now click schedule. I change this to run every hour again – the default is 15 minutes. This time I will also change the start time to 00:15. There is no point it trying to copy the files on the hour as the actual backup is being taken on the hour. Click Ok and that task will be scheduled.
Now click on the Restore Transaction Job task. I change the delay option to 5 minutes and the alert option to 2 hours. This is because I have a slow rate of change and so only copy every hour. Click on schedule again. This time I will set the task to run at 30 minutes past the hour and to run hourly. Click ok and then on the next screen click on again.
You are now on the properties page again.
Click ok again and the server will create the log shipping jobs. Once the two items have gone green you can click close.
Run the Log Shipping Jobs.
On the primary server there will be a job named like LSBackup_DatabaseName.
Run the LSBackup_DatabaseName job and check that a trn backup file has appeared in the folder you created on the primary server.
On the secondary server look for a job like LSCopy_PrimaryServerName_DatabaseName and run this job. You should see the trn file appear in th efoledr you created on ServerB. Now run LSRestore_PrimaryServerName_DatabaseName.
Finally Transfer Logins
You should use SSIS to create a transfer logins task to copy the logins from serverA to serverB. This way if you have to fail the database over to serverB you can enable to logins and won't have any permissions problems.
Subscribe to:
Posts (Atom)