02/10/10 70-431 Score: 1000 (Non-performance), 940 (Performance)
02/17/10 70-444 Score: 968
02/22/10 70-443 Score: 955
02/27/10 70-441 Score: 983
02/27/10 70-442 Score: 940
03/01/10 70-453 Score: 1000 (design/optimize/maintain), 972 (implement)
03/01/10 70-454 Score: 966 (DB Development), 1000 (Design and Data Access)
06/10/10 70-445 Score: 1000
06/10/10 70-455 Score: 976
06/11/10 70-446 Score: 984
Wow!!! I am now a Microsoft Certified IT Professional in SQL Server Administration and Development (2005/2008), and Microsoft Business Intelligence Development (2005/2008).
How to change the SQL Server stored package?
Cannot do it directly in the MSDB folder or the package store(the File System Folder) in SSMS/SSIS. Have to export it first. Change it in BIDS, then re-import it back to SQL Server/SSIS/MSDB.
Note: If the exported package file is double-clicked, the Execute Package Utility is opened. But it's hard to fix/change the connection managers, control flow/data flow tasks on this interface. It's better to edit it in BIDS.
Note: If the exported package file is double-clicked, the Execute Package Utility is opened. But it's hard to fix/change the connection managers, control flow/data flow tasks on this interface. It's better to edit it in BIDS.
Many table changes from AdventureWorks to AdventureWorks2008
e.g., person.contact is replaced with person.person. click the link below for more details.
http://msftdbprodsamples.codeplex.com/wikipage?title=AW2008Details&referringTitle=Home
http://msftdbprodsamples.codeplex.com/wikipage?title=AW2008Details&referringTitle=Home
Access is denied when executing a package with DTExecUI
Package execution is fine in BIDS as an admin. But I encountered an error by using the DTExecUI interface (e.g., access is denied to update the checkpoint file). In this case, I need to run the command prompt with the admin authority.
How to remove recent projects from Visual Studio Start Page
(source: http://aspnetcoe.wordpress.com/2007/02/06/how-to-remove-recent-projects-from-visual-studio-start-page/)
To remove the projects from the list, follow these steps:
1.Close Visual Studio if it is running.
2.Start the Registry Editor (run regedit).
3.Navigate to this registry key:
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList
4.Then delete the key that has the project you do not want to keep in the list.
Caveat: The list only lists consecutive items starting from File1. So if in the above list I deleted File6, then only projects from File1 to File5 will be displayed in the recent project list. File7 and above will not be displayed. If you like File7 and above to be displayed in this case, you will need to rename one of the keys so that they form a consecutive numbered list.
To remove the projects from the list, follow these steps:
1.Close Visual Studio if it is running.
2.Start the Registry Editor (run regedit).
3.Navigate to this registry key:
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList
4.Then delete the key that has the project you do not want to keep in the list.
Caveat: The list only lists consecutive items starting from File1. So if in the above list I deleted File6, then only projects from File1 to File5 will be displayed in the recent project list. File7 and above will not be displayed. If you like File7 and above to be displayed in this case, you will need to rename one of the keys so that they form a consecutive numbered list.
How to make the ForEach Loop Container work - the project on pp 116-117
First, you need to change the path for the 3 excel files from c:\users\Administrator\... to wherever you actually store the files (e.g., c:\user\yourname\...) in both the ForEach container and the Excel Connection Manager properties (including Excel Source if necessary). Then, for the archive Connection Manager, you can create a folder. Finally, for the File System Task, you should use 'Copy File' for Operation and 'Archive' for 'DestinationConnection'. In this way, all of the 3 files are looped.
Update:
The previous approach does not use the property expression to update the Excel Connection Manager dynamically. If using the property expression as that on p. 117, the Excel Source component in the Data Flow tab will have an error icon for invalid connection (OLE DB Error), just ignore it as the DelayValidation property for the Foreach Loop container is set to True.
More updates on the project (5/15/2011):
1. To run 32-bit in 64-bit SSIS: Project-->xxx Project Properties-->Debugging-->run 64-bit FALSE
2. many warning and error messages related to SSIS Logging and Package Configurations (as I use 2008R2 not 2008). Need to fix them as well.
3. The project basically moves 3 excel files from the location specified in the LoopEach container to the location specified in the Archive connection manager (using copy not move)
4. The definition of the User Variable [User:FileName] may need to be changed well for a valid path.
Update:
The previous approach does not use the property expression to update the Excel Connection Manager dynamically. If using the property expression as that on p. 117, the Excel Source component in the Data Flow tab will have an error icon for invalid connection (OLE DB Error), just ignore it as the DelayValidation property for the Foreach Loop container is set to True.
More updates on the project (5/15/2011):
1. To run 32-bit in 64-bit SSIS: Project-->xxx Project Properties-->Debugging-->run 64-bit FALSE
2. many warning and error messages related to SSIS Logging and Package Configurations (as I use 2008R2 not 2008). Need to fix them as well.
3. The project basically moves 3 excel files from the location specified in the LoopEach container to the location specified in the Archive connection manager (using copy not move)
4. The definition of the User Variable [User:FileName] may need to be changed well for a valid path.
Some Interview Questions - how to handle the large amount of deletion and how to handle the suspect mode of a database
Question
1: How to delete 10 million rows in the full mode?
Step 1 -
deleting in chunks - maybe 10,000 records per time (divide and conquer)
Step 2 -
keeping the row counts
Step 3 -
using dbcc Shrinkfile as below (see http://support.microsoft.com/kb/907511)
To
shrink a transaction log file that has little free space in SQL Server 2005,
follow these steps:
1)
Back up the transaction log file to make most of the active
virtual log files inactive. Therefore, the inactive virtual log files can be
removed in a later step. To do this, run a Transact-SQL statement that is
similar to the following Transact-SQL statement.
BACKUP
LOG TestDB TO DISK='C:\TestDB1.bak'
2)
Shrink the transaction log file. To do this, run a Transact-SQL
statement that is similar to the following Transact-SQL statement.
BCC
SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
Note:
In this statement, is a placeholder for the name of the transaction log
file. In this statement, is a placeholder for the target size that you
want the transaction log file to be. The target size must be reasonable. For
example, you cannot shrink the transaction log file to a size that is less than
2 virtual log files.
3)
If the DBCC SHRINKFILE statement does not shrink the transaction
log file to the target size, run the BACKUP LOG statement that is mentioned in
step 1 to make more of the virtual log files inactive.
4)
Run the DBCC SHRINKFILE statement that is mentioned in step 2.
After this operation, the transaction log file should be similar to the target
size.
Question
2: How to handle the suspect mode?
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
Subscribe to:
Posts
(
Atom
)