Database Mail in 2005/2008 with Gmail
It works! I can use smtp.gmail.com as the outgoing mail server by setting port=587 (the default port=25 is usually blocked by Windows firewall)
How to Create a Performance Counter Log in Windows 7
1. Open Performance Monitor, and navigate to the Data Collector Sets node in the tree on the left.
2. Right-click User Defined, and navigate the menu to create a new data collector set.
3. Give the new set a name, select the Create Manually option button, and click Next.
4. Choose the Create Data Logs radio button, select the Performance Counter check box, and then click Next.
5. Click Next again. It’s not the time yet to select the performance counters, but you can customize the sampling time interval for the performance counter log.
6. Specify a directory where you want the logs to be saved, and then click Next.
7. Click Finish.
2. Right-click User Defined, and navigate the menu to create a new data collector set.
3. Give the new set a name, select the Create Manually option button, and click Next.
4. Choose the Create Data Logs radio button, select the Performance Counter check box, and then click Next.
5. Click Next again. It’s not the time yet to select the performance counters, but you can customize the sampling time interval for the performance counter log.
6. Specify a directory where you want the logs to be saved, and then click Next.
7. Click Finish.
SQL Server Error - How to rebuild the system databases
- Need to stop both SQL Server Services and SQL Server Agent Services
- Find sqlservr.exe. It is in different folders in 2005 and 2008.
- In 2005: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
- in 2008: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServre\MSSQL\Binn
- If there are errors, especially in 2008 such as InitErrLog, run the command prompt as an administrator.
- Run the command in single user mode: sqlservr -m, then use the setup.exe option to rebuild one of the system databases (see 2005 DBD book p.541 for details)
Need to run the TableDiff command from the right directory
TableDiff command needs to be in the right directory. For example,
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SQL2005" -sourcedatabase "MyDB" -sourcetable "ABC" -destinationserver "SQL2005" -destinationdatabase "MyDB" -destinationtable "ABC_New"
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SQL2005" -sourcedatabase "MyDB" -sourcetable "ABC" -destinationserver "SQL2005" -destinationdatabase "MyDB" -destinationtable "ABC_New"
SQL Server 2008 R2 Enterprise cannot be installed in Windows 7, but the Developer version is OK - see software requirements.
http://technet.microsoft.com/en-us/library/ms143506(sql.105).aspx
How to make the chart still appearing after the table data is hidden in Excel 2007
1. Right-click on your chart and choose 'Select Data'
2. Click the 'Hidden and Empty cells' button in the lower left
3. Check the box for 'Show data in hidden rows and columns', click OK twice.
4. Hide your data.
Reference:
http://www.excelforum.com/excel-charting/658523-chart-disappears-when-i-hide-columns-with-values.html
2. Click the 'Hidden and Empty cells' button in the lower left
3. Check the box for 'Show data in hidden rows and columns', click OK twice.
4. Hide your data.
Reference:
http://www.excelforum.com/excel-charting/658523-chart-disappears-when-i-hide-columns-with-values.html
Deploying SSRS Error - The model ID of the submitted model must match that of the current model.
Problem:
When I deploy a report model, it shows a deployment error: The model ID of the submitted model must match that of the current model.
Reason:
Because I try to deploy a model in a NEW report project which has the same model name in a previous report project.
Solution:
Go to the reporting web site (http://localhost/reports), go to the models folder and delete the
model item. Then re-deploy it.
When I deploy a report model, it shows a deployment error: The model ID of the submitted model must match that of the current model.
Reason:
Because I try to deploy a model in a NEW report project which has the same model name in a previous report project.
Solution:
Go to the reporting web site (http://localhost/reports), go to the models folder and delete the
model item. Then re-deploy it.
My MCITP Transcripts
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).
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
SQL 2008 Connection Problem
Everything has been fine in connecting to SQL Server 2008 default and the named instances. But today the default instance cannot be connected with an error:
==================================================
TITLE: Connect to Server
------------------------------
Cannot connect to XU-PC.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
===================================================
But it is OK to connect to the named instance and SSIS/SSRS/SSAS.
Reason: the default instance service, which has been configured automatically starting, was not started for some reason.
==================================================
TITLE: Connect to Server
------------------------------
Cannot connect to XU-PC.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
===================================================
But it is OK to connect to the named instance and SSIS/SSRS/SSAS.
Reason: the default instance service, which has been configured automatically starting, was not started for some reason.
System Environment Variable issue
After the System Environment Variable is created, need to restart BIDs to use it.
SSIS Script Task Error
Problem:
Today, I encounter the error as below in editing the Script Task:
Cannot show Visual Studio 2008 Tools for Applications Editor.
Additional Information:
The System Cannot Find The File Specified (Exception from HRESULT: 0x80070002) (EnvDTE80)
Solution:
The following solution fixed the problem from http://www.sqldev.org/sql-server-integration-services/cannot-show-visual-studio-tools-for-application-editor-1076.shtml
=======================
I've used the following command script to clean upcached VSTA configurations for SSIS with success. From a command prompt (under WoW32 on 64 bit platforms)with admin privileges (make sure to run the command prompt as an administrator if you have UAC enabled)run a batch file with the following content:
@rem start batch file
@rem delete the VSTA registrycache from the current user
REG DELETE HKCU\Software\Microsoft\VSTA /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
@rem delete the VSTA registration cache for SSIS script task and data flow script component
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
@rem delete the cached files from the HDD
rd /s /q "%AppData%\Microsoft\VSTA"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptTask"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTA"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptComponent"
@rem re-generate the VSTA files for SSIS script task and component
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptTask /setup
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptComponent /setup
@rem end batch file
=================
Today, I encounter the error as below in editing the Script Task:
Cannot show Visual Studio 2008 Tools for Applications Editor.
Additional Information:
The System Cannot Find The File Specified (Exception from HRESULT: 0x80070002) (EnvDTE80)
Solution:
The following solution fixed the problem from http://www.sqldev.org/sql-server-integration-services/cannot-show-visual-studio-tools-for-application-editor-1076.shtml
=======================
I've used the following command script to clean upcached VSTA configurations for SSIS with success. From a command prompt (under WoW32 on 64 bit platforms)with admin privileges (make sure to run the command prompt as an administrator if you have UAC enabled)run a batch file with the following content:
@rem start batch file
@rem delete the VSTA registrycache from the current user
REG DELETE HKCU\Software\Microsoft\VSTA /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
@rem delete the VSTA registration cache for SSIS script task and data flow script component
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
@rem delete the cached files from the HDD
rd /s /q "%AppData%\Microsoft\VSTA"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptTask"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTA"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptComponent"
@rem re-generate the VSTA files for SSIS script task and component
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptTask /setup
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptComponent /setup
@rem end batch file
=================
"http://localhost/reports" - "HTTP Error 503. The service is unavailable."
Problem:
Earlier "http://localhost/reports" worked fine in Windows 7. But, today, "HTTP Error 503. The service is unavailable."
Solution:
After deleting the testUser (standard account) from User Account, it works fine. This is why: Although it appears that I have logged in with the admin account, not the testUser account, but I have used the testUser account for testing before. The system for some reason has used the cached testUser account, instead of the desired admin account in opening the report manager.
Earlier "http://localhost/reports" worked fine in Windows 7. But, today, "HTTP Error 503. The service is unavailable."
Solution:
After deleting the testUser (standard account) from User Account, it works fine. This is why: Although it appears that I have logged in with the admin account, not the testUser account, but I have used the testUser account for testing before. The system for some reason has used the cached testUser account, instead of the desired admin account in opening the report manager.
SQL Server 2008 Report Server - The Properties option is greyed out
Sometimes, when I right click the Reporting Server in SQL Server 2008 in Windows 7, the Properties option is dimmed. This problem is not in Windows XP. Tried uninstall/reinstall, doesn't help. To fix it, need to run SQL server 2008 as an admin in Win 7 explicitly.
How to run as a non-admin user in Windows 7 for SQL Server 2008 (or any application)
1. Press and Hold the Shift Key
2. right click the application
3. "Run as different user" appears now.
2. right click the application
3. "Run as different user" appears now.
Either the user xxx\xxx does not have permissions to access to the referenced mining structure, xxx, or ..... for the DMX Query
Need the semicolon (;) for each statement and execute the statements one at a time.
"Error Loading mining model metadata: No mining models were found." for a new DMX Query
If you see the above error message when performing step 3 on p.427, you need to choose the right database from the drop-down listbox on the left of the Execute botton on the tool bar, just like you typically switch b/t adventureworks and master databases in SSMS. Then the error message will be gone.
The "Show Historic Prediction" checkbox is not shown on the mining model views of a time series prediction
1. Got to the Mining Models tab for the Time Series Prediction model in the design mode.
2. Right click ->Set Algorithm Parameter
3. Set the Historical_Model_GAP to a number (e.g., 6 for 6 time slices between historical models on p.418 of the 70-448 Self-paced Training Kit book)
But if you set it to 1 or >7, the Show Historic Predictions checkbox will not be shown, that's why initially the option was not available as the default value is 10.
2. Right click ->Set Algorithm Parameter
3. Set the Historical_Model_GAP to a number (e.g., 6 for 6 time slices between historical models on p.418 of the 70-448 Self-paced Training Kit book)
But if you set it to 1 or >7, the Show Historic Predictions checkbox will not be shown, that's why initially the option was not available as the default value is 10.
The accuracy of the results in Data Mining Viewer
In doing the Exercise 6 in the 70-448 self-paced training kit (p.391), I found that my results in the Data Mining Viewer in step 2 and step 3 (with total children instead of age) are different from the book sample. Later, I realize my case sample has a different composition from the book (p.383): 6536 vs. 6403, comparing with 6509 vs. 6430 in the book. Since I did not change the vTargetMail data, I suspect these differences come from the 70% random sampling in creating the mining structure model. Now the issue is: If the results are affected by the random sampling for the training process, how reliable of the model results?
Fatal Internal Error on the Analysis Server Properties dialogbox - restart the SSAS
If you tried to change the four QueryLog values on the Analysis Server Properties dialogbox and a Fatal Internal Error when clicking OK to save the changes. Just click 'Reset default' and restart the SSAS. Then you can change the property values.
"Access is denied" on msmdsrv.ini
Today, I try to see the feature settings in msmdsrv.ini file in the OLAP\Config folder. However, I get an "Access is denied" message. The login ID has the admin right. Under the folder, there are msmdsrv.ini and msmdsrv.bak files (do not know when the system created the .bak file and why). The .bak file is newer than the .ini file. Finally, I moved the .ini to somewhere else and renamed the .bak file to .ini file. The problem is solved.
Subscribe to:
Posts
(
Atom
)