Problem:
I recently changed my PC name. But the login in SQL Server still uses the old pc name. I decided to delete the login. Bad for me, the deleted login was the only one with sysadmin rights in SQL Server. The sa was not enabled. No other sysadmin logins. So I was completely locked out off the box. I cannot be back to SQL Server anymore with any existing logins.
Trial:
I switched to other windows logins with admin rights. Unfortunately, although they log into SQL Server, but they did not have the sysadmin rights. So they cannot create new sysadmin logins or enable the sa account.
Solution:
1. Followed the steps in this article - http://www.mssqltips.com/sqlservertip/2465/how-to-connect-to-sql-server-if-you-are-completely-locked-out/
(1) stop the SQL Server service
(2) login as a single user
(3) use SQLCMD -E (yes, I logged into as a local windows admin)
(4) create a SQL Server login, add it to the sysadmin role.
2. Start the SQL Server service again
(1) get an error for time out using the local system account when starting the service
(2) try newly created SQL server login to start the SQL Server service, wow, it worked!
3. Login to SQL server with the new login created in the SQLCMD
4. create a login based on the Windows login with the correct PC name (a windows admin) with sysadmin role
5. Login into SQL Server in Windows authentication, it worked!
Lessons Learned:
Pause, think, backup, test before making any changes in SQL Server. A seemly trivial change may eat up hours of your time if not done appropriately.
Error in Executing a SQL Server Agent Job
Problem:
Executed as NT AUTHORITY\SYSTEM. Got an error: Invalid object name 'sysjobs'. [SQLSTATE 42S02] (Error 208).
Solution:
Just need to fully qualify it as msdb.dbo.sysjobs
Executed as NT AUTHORITY\SYSTEM. Got an error: Invalid object name 'sysjobs'. [SQLSTATE 42S02] (Error 208).
Solution:
Just need to fully qualify it as msdb.dbo.sysjobs
Several Issues on Installing SQL Server 2005 on Windows 7
Scenario:
I have 64-bit Windows-7 with SQL Server 2000, 2008, 2008R2, and 2012 installed. In order to check if the undocumented function sys.fn_PhysLocFormatter runs in SQL Server 2005. I decided to install SQL Server 2005 (64-bit) to the same box as well. After installation, I encounter the following issues:
Problem 1:
When log into Database engine, login failed for machinename\theWinLogin, which has the admin right.The application log basically indicates the SQL Server Service account (local system account) does not have the permission to run the service. Weird.
Solution:
Run SQL Server explicitly as an admin.
Problem 2:
The SQL Server FullText Search service, for some reason, cannot be started. Got a timeout error.
Solution:
After installing SP4, it was OK.
Problem 3:
Facing some problems about 64-bit ASP.NET and 64-bit COM+.
Reason:
Basically I have 32-bit installed and used before. But SQL Server 2005 64-Bit needs the 64-bit.
Solution:
Followed the steps in the posts below, the problem is solved.
http://www.expta.com/2007/11/installing-or-switching-aspnet-versions.html
http://codeasp.net/blogs/vivek_iit/microsoft-net/440/sql-server-install-error-one-or-more-of-the-components-being-installed-are-already-registered-as-32-bit-components-in-the-target-application
I have 64-bit Windows-7 with SQL Server 2000, 2008, 2008R2, and 2012 installed. In order to check if the undocumented function sys.fn_PhysLocFormatter runs in SQL Server 2005. I decided to install SQL Server 2005 (64-bit) to the same box as well. After installation, I encounter the following issues:
Problem 1:
When log into Database engine, login failed for machinename\theWinLogin, which has the admin right.The application log basically indicates the SQL Server Service account (local system account) does not have the permission to run the service. Weird.
Solution:
Run SQL Server explicitly as an admin.
Problem 2:
The SQL Server FullText Search service, for some reason, cannot be started. Got a timeout error.
Solution:
After installing SP4, it was OK.
Problem 3:
Facing some problems about 64-bit ASP.NET and 64-bit COM+.
Reason:
Basically I have 32-bit installed and used before. But SQL Server 2005 64-Bit needs the 64-bit.
Solution:
Followed the steps in the posts below, the problem is solved.
http://www.expta.com/2007/11/installing-or-switching-aspnet-versions.html
http://codeasp.net/blogs/vivek_iit/microsoft-net/440/sql-server-install-error-one-or-more-of-the-components-being-installed-are-already-registered-as-32-bit-components-in-the-target-application
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Problem:
I am trying to validate the codes in "Script to auto generate a security report for your SQL Server instance" by Michelle Gutzait on SQL Server 2008R2, I get an error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Reason:
My server has a collation: Latin1_General_CI_AS
The Database context runs the code has a collation: Latin1_General_CP1_CI_AS
That causes the problem.
Solution:
Run the following code to make the DB collation in sync with the server collation. After that, it works!
USE Tempdb
GO
ALTER DATABASE (database name here) COLLATE (collation name here)
GO
I am trying to validate the codes in "Script to auto generate a security report for your SQL Server instance" by Michelle Gutzait on SQL Server 2008R2, I get an error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Reason:
My server has a collation: Latin1_General_CI_AS
The Database context runs the code has a collation: Latin1_General_CP1_CI_AS
That causes the problem.
Solution:
Run the following code to make the DB collation in sync with the server collation. After that, it works!
USE Tempdb
GO
ALTER DATABASE (database name here) COLLATE (collation name here)
GO
Trying PSExec to access to SQL Server 2008+ if locked out
See the following article by Aaron Bertrand for background information on the PSExec utility.
http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe (http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx). Run the command prompt (i.e., cmd) as an Administrator. Run the following command, adjusting for your actual path to Management Studio:
http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe (http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx). Run the command prompt (i.e., cmd) as an Administrator. Run the following command, adjusting for your actual path to Management Studio:
PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe". It worked!
(issuing the PsExec command from the right directory. In my case, it is: C:\Users\CK\Downloads\PSTools\PsExec -s -i.....)
How to Install 2008 Reporting Services Samples on SQL Server 2008R2
Scenario:
As SQL Server moves to 2008R2 and 2012, it is a little bit hard to find the reporting services samples for SQL Server 2008. Recently I have a need to install 2008 reporting service samples on a Win7/SQL Server 2008R2(2012 also)/VS9(VS10 also) PC. I already have the R2 samples installed.
Step 1: Download the 2008 sample files from
http://msftrsprodsamples.codeplex.com/releases/view/34047
I used the middle zip file
Step 2. Unzip it to a different location. Mine is:
C:\Users\CK\Downloads\SQL2008.Reporting_Services.Samples\Samples\Reporting Services\Report Samples\AdventureWorksOffline Sample Reports\ChartSamples
Step 3. Open the offline sample file from File/Open/Project in VS 9.
Step 4. There is a little bit problem with the Data_AddingCalculatedSeries.rdl file. I simply copied the Data_AddingCalculatedSeries2008R2 file from the R2 folder and to the above folder in step 2 and renamed it to Data_AddingCalculatedSeries.
Step 5. Previewed, everything is OK! Now I have both 2008 and 2008R2 RS samples on the PC.
As SQL Server moves to 2008R2 and 2012, it is a little bit hard to find the reporting services samples for SQL Server 2008. Recently I have a need to install 2008 reporting service samples on a Win7/SQL Server 2008R2(2012 also)/VS9(VS10 also) PC. I already have the R2 samples installed.
Step 1: Download the 2008 sample files from
http://msftrsprodsamples.codeplex.com/releases/view/34047
I used the middle zip file
Step 2. Unzip it to a different location. Mine is:
C:\Users\CK\Downloads\SQL2008.Reporting_Services.Samples\Samples\Reporting Services\Report Samples\AdventureWorksOffline Sample Reports\ChartSamples
Step 3. Open the offline sample file from File/Open/Project in VS 9.
Step 4. There is a little bit problem with the Data_AddingCalculatedSeries.rdl file. I simply copied the Data_AddingCalculatedSeries2008R2 file from the R2 folder and to the above folder in step 2 and renamed it to Data_AddingCalculatedSeries.
Step 5. Previewed, everything is OK! Now I have both 2008 and 2008R2 RS samples on the PC.
Cannot open Visual Studio 2008 files after installing 2010
Problem:
Recently, I encountered a problem similar to that in this thread.
Solution:
Opening the project file instead of the solution file works for me!
Recently, I encountered a problem similar to that in this thread.
Solution:
Opening the project file instead of the solution file works for me!
Asking for User Name and Password when connecting to Report Manager
Problem:
When I openned http://localhost/reports, I got the username/pw dialog. This post basically says that if you can connect to SSRS via SSMS but cannot connect to report manager, you could try to remove RSWindowsNegotiate and ensure RSWindowsNTLM is specified in the file rsreportserver.config. But it did not help.
Solution:
Run the IE as an admin.
When I openned http://localhost/reports, I got the username/pw dialog. This post basically says that if you can connect to SSRS via SSMS but cannot connect to report manager, you could try to remove RSWindowsNegotiate and ensure RSWindowsNTLM is specified in the file rsreportserver.config. But it did not help.
Solution:
Run the IE as an admin.
Some Issues on Installing and Connecting to SSRS 2005 on Windows XP
I have SQL Server 2005 SP3 (9.0.4053) installed on XP SP3 but no SSRS. So I decide to install SSRS.
Step 1. Install IIS - OK
Step 2. Install SSRS - OK
Step 3. Configure Reporting Services
Step 1. Install IIS - OK
Step 2. Install SSRS - OK
Step 3. Configure Reporting Services
Problem on Database Setup
Solution:
Need to run SP3 again to update the newly installed SSRS for the reportserver databaseStep 4. Connecting to SSRS
Problem:
No connection could be made because the target machine actively refused it 127.0.0.1:80
Troubleshooting
- Checked: remote connection enabled, TCP/IP enabled, SQL Browsing running, restarted SQL Server service, but still the same
- Back to IIS Installation (from Add/Remove Windows Components in XP), Under World Wide Web, selected Remote Desktop Web Connections. Now the error message is:
The new error message helps! The message basically indicates the problem is related to the mapping between IIS and ASP.NET (because the .NET framework was installed first, IIS was installed later). This issue has been addressed by MS KB 306005.
Solution
Run the following DOS command:
"C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe" -i
Set up Peer-to-Peer Replication in SSMS
One key part is to set the property values properly.
To enable the publication for peer-to-peer replication
To enable the publication for peer-to-peer replication
- Expand the Replication folder on Server A, and then expand the Local Publications folder.
- Right-click and set up as below
Copy a PDF Figure to MS Word
Some PDF documents do not let the user copy the figures (but copying text is OK). How do copy the figure or graph from PDF to Word in this case?
For Adobe Reader 10+
Edit --> Take a Snashot
A related issue: Is it possible to keep the formatted SQL Server codes in the PDF file and copy them to SSMS or Word?
The answer basically is NO, You cannot do it due to the nature of the PDF document, even you use PDF Professional or a program converting from PDF to Word. Taking a snapshot will give you the visual presentation of the formatted codes, but not a formatted text.
For Adobe Reader 10+
Edit --> Take a Snashot
A related issue: Is it possible to keep the formatted SQL Server codes in the PDF file and copy them to SSMS or Word?
The answer basically is NO, You cannot do it due to the nature of the PDF document, even you use PDF Professional or a program converting from PDF to Word. Taking a snapshot will give you the visual presentation of the formatted codes, but not a formatted text.
Set the Collate to Latin1_General_CI_AS for SQL Sever for the sake of Team Foundation Server
I tried to install Team Foundation Server 2010 on my laptop earlier today and encountered an error below:
Error [ Configuration Database ] TF255374: The following SQL Server instance is not compatible with Team Foundation Server: CK-PC. The default collation is set to Latin1_General_CI_AI. Team Foundation Server requires that the default collation be case insensitive and accent sensitive. You must choose a SQL Server instance whose default collation settings match these requirements.
Obviously one way to fix the problem seems to be rebuild the system databases with the required collation and re-install user databases. But it will take a while.
So the key takeaway is: if you need to use Team Foundation Server with SQL Server, find TFS' default collation first and then use the same collation when you install SQL Server.
Connecting to Reporting Services in SSMS as an admin - If you do not see some options are disabled
If the options or menus for the jobs, security or shared schedules etc. are disabled, then you need to connect to SSRS via SSMS as an admin.
Problems related to Exercises 1-3 in pp. 557 - 560 (the TK 70-448 book)
I keep getting the error message: an error occurred during client rendering an error occurred during report processing. Cannot create a connection to data source 'xxxxx'.
To fix the problem, change the instance to the mixed mode authentication. In this way, the SQL Server login ReportExecution should be fine.
Note 1: The database should the AdventureWorks2008 or AdventureWorks2008R2, not the DW ones.
Note 2: The user in the msdb does not need to be in the db_datawriter role.
Note 3: The UNC path for a local folder could be: \\MachineName\theSharedFolder, localhost should be OK too to replace the MachineName.
Note 4: The credential for accessing the shared folder on p.560 should be the Windows user created earlier.
To fix the problem, change the instance to the mixed mode authentication. In this way, the SQL Server login ReportExecution should be fine.
Note 1: The database should the AdventureWorks2008 or AdventureWorks2008R2, not the DW ones.
Note 2: The user in the msdb does not need to be in the db_datawriter role.
Note 3: The UNC path for a local folder could be: \\MachineName\theSharedFolder, localhost should be OK too to replace the MachineName.
Note 4: The credential for accessing the shared folder on p.560 should be the Windows user created earlier.
Finally solved- "Error while loading code module: 'ReportCustomMethods, version=0.0.1.1, culture=neutral, PublicKeyToken=null' Details: Could not load file or assembly "TK 448 Ch11 CustomAssembly", Version=0.0.1.1 ... or one of its dependencies. The system cannot find the file specified."
Problem:
I have both VS 2008 and 2010 on my Win 7 PC, and have used VS 2008 and SQL Server 2008R2 for Exercise 4 - Use Methods from a Custom Assembly on p.514 of the TK-448 book, but I got the error as in the title.
Solution:
Copy the .dll file to the right place as below:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
(not the one in C:\Program Files\...........)
I have both VS 2008 and 2010 on my Win 7 PC, and have used VS 2008 and SQL Server 2008R2 for Exercise 4 - Use Methods from a Custom Assembly on p.514 of the TK-448 book, but I got the error as in the title.
Solution:
Copy the .dll file to the right place as below:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
(not the one in C:\Program Files\...........)
Errors in the encryption library: Failed to encrypt sensitive data. Possibly the encryption key is inaccessible because of improper service account change.
Problem:
Encounter the error as shown in the titile when processing a cube.
Solution:
- Stop the SSAS service
- Make a backup of the project: C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\TK 448 Mining Models Project.0.db
- Delete SSAS database, that is, delete the contents of C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\TK 448 Mining Models Project.0.db directory.
- Delete the "TK 448 Mining Models Project.0.db.xml" from the project data folder "..Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\"
- Start the SSAS service again and deploy/process your cube again using BIDS.
How to Fix the problem - No Data for the 5 views in AdventureWorksDW2008R2
Problem:
Today, I found there are no data for the 5 views in the AdventureWorksDW2008R2 database.
Solution:
- Detach the current AdventureWorksDW2008R2 (disconnect the current connection first if necessary)
- Run the codes below in the Instawdwdb file (from the downloaded AdventureWorks 2008R2 Data Warehouse) in SQLCMD mode.
- Change the file path if necessary based on the error messages.
Note:
:setvar SqlSamplesDatabasePath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
(the mdf and ldf files may need to be deleted from this location for re-run)
:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Data Warehouse\" (the last part is the path on my machine - this is where all of the data files are)
/*======================================================================
File: instawdwdb.sql
Summary: Creates the AdventureWorks 2008R2 data warehouse sample database.
Date: June 14, 2008
Updated: March 28, 2012
-- TODO: Verify
SQL Server Version: 10.50.1600
------------------------------------------------------------------------------
This file is part of the Microsoft SQL Server Code Samples.
Copyright (C) Microsoft Corporation. All rights reserved.
This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation. See these other
materials for detailed information regarding Microsoft code samples.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
=======================================================================*/
-->> WARNING: THIS SCRIPT MUST BE RUN IN SQLCMD MODE INSIDE SQL SERVER MANAGEMENT STUDIO. <<--
:on error exit
/*
* In order to run this script manually, either set the environment variables,
* or uncomment the setvar statements and provide the necessary values if
* the defaults are not correct for your installation.
*/
:setvar SqlSamplesDatabasePath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Data Warehouse\"
===================
The rest are omitted
Why need the script task to show up all of the three files for the ForEach container?
Public Sub Main()
'
Dts.Connections("Excel Connection Manager").ConnectionString() = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Dts.Variables("FileName").Value.ToString() + ";Extended Properties=""EXCEL 8.0;HDR=YES"";"
'
Dts.TaskResult = ScriptResults.Success
End Sub
If the Script Task is disabled, the 1st Excel file will be processed three times (There are three files in the folder).
With the Script Task, the three Excel files are processed as desired.
The is the Data Flow Part:
Losing Formats of TSQL Scripts from SSMS to MS Word
Problem:
Losing Formats of TSQL Scripts from SSMS to MS Word
Solution:
Close Google Chrome and Skype. See below
Reference:
http://answers.microsoft.com/en-us/office/forum/office_2010-word/losing-formatting-when-i-copy-and-paste-from-one/7e1b2101-e431-4b29-aa58-15d5161cbd69?msgId=f3085111-23af-4ed7-b822-0688a97b6f18
Losing Formats of TSQL Scripts from SSMS to MS Word
Solution:
Close Google Chrome and Skype. See below
Reference:
http://answers.microsoft.com/en-us/office/forum/office_2010-word/losing-formatting-when-i-copy-and-paste-from-one/7e1b2101-e431-4b29-aa58-15d5161cbd69?msgId=f3085111-23af-4ed7-b822-0688a97b6f18
What is 'index ANDing" exactly?
ANDing means when multiple indexes are used in a single hint, the query optimizer enforce the order of the indexes in the index hints, ANDing applies as many conditions as possible on each index accessed.
INDEX (index_value [,... n ] ) | INDEX = ( index_value)
The INDEX() syntax specifies the names or IDs of one or more indexes to be used by the query optimizer when it processes the statement. The alternative INDEX = syntax specifies a single index value. Only one index hint per table can be specified.
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
If multiple indexes are used in a single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing, and the query optimizer applies as many conditions as possible on each index accessed. If the collection of hinted indexes do not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns.
=== More from BOL===
INDEX (index_value [,... n ] ) | INDEX = ( index_value)
The INDEX() syntax specifies the names or IDs of one or more indexes to be used by the query optimizer when it processes the statement. The alternative INDEX = syntax specifies a single index value. Only one index hint per table can be specified.
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
If multiple indexes are used in a single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing, and the query optimizer applies as many conditions as possible on each index accessed. If the collection of hinted indexes do not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns.
The "Create" button for new shared dataset in Report Builder 3.0 is dimmed
Solution:
You must have a shared data source first in order to create a shared dataset. So do that part first.
You must have a shared data source first in order to create a shared dataset. So do that part first.
A Report Builder 2 Error
I created a sub-report in Report Builder 2. It works fine in Report Manager, but it shows the following error in Report Builder 2.0
This report cannot be run in report builder because it contains one or more embedded data sources with credential options that are not supported. Instead of embedded data sources use shared data sources or save and view the report on the server.
This may be a bug. See the blog below.
http://blog.hoegaerden.be/2009/09/12/report-builder-2-0-cannot-switch-data-source/
This report cannot be run in report builder because it contains one or more embedded data sources with credential options that are not supported. Instead of embedded data sources use shared data sources or save and view the report on the server.
This may be a bug. See the blog below.
http://blog.hoegaerden.be/2009/09/12/report-builder-2-0-cannot-switch-data-source/
How to save to Report Server from Report Builder 2.0
1. Report builder 2.0 Ribbon
2. Options
3. In the first box, enter http://localhost/reportserver
Note: (1). Not working if no "http://" (i.e., localhost/reportserver is not enough)
(2). Shared dataset is a RB3 thing, not possible in RB2.
Note: (1). Not working if no "http://" (i.e., localhost/reportserver is not enough)
(2). Shared dataset is a RB3 thing, not possible in RB2.
Change the Collation for the Instances in SQL Server 2008R2
Problem:
During the installation, I accidently used the Chinese_PRC...as the default collation, I actually wanted to use Latin1_General_CI_AI for the default instance and Instance2 on the x64 Win 7 machine. I do not want to go through the time-consuming uninstall and re-install process, for just changing the collation. So I chose the option to rebuild the system databases. Then change the collation as follows.
Solution:
Issued the following command with the admin priviledge:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer
/SQLSYSADMINACCOUNTS="xxx\CK" /SQLCOLLATION=Latin1_General_CI_AI
NOT WORKING! Using the Installation path, it worked:
H:\downloads\SQL Server 2008 R2 Developer Edition\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS="xxx\CK" /SQLCOLLATION=Latin1_General_CI_AS
For the 2nd Instance named Instance (notice NOT "MachineName\Instance2" or others, just Insatnace2):
H:\downloads\SQL Server 2008 R@ Developer Edition\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Instance2
/SQLSYSADMINACCOUNTS="xxx\CK" /SQLCOLLATION=Latin1_General_CI_AI
Not surprisingly, the AdventureWorks sample databases are gone, I need to restore them.
However, when I tried to re-install them, the sample databases not showing up. Deleted the mdf data files, tried again, showed some DBs, not all of them. Finally, removed the files from .../Data and .../tool/sample (created for the installation in the early process) folders, restore them again, Aha!.
During the installation, I accidently used the Chinese_PRC...as the default collation, I actually wanted to use Latin1_General_CI_AI for the default instance and Instance2 on the x64 Win 7 machine. I do not want to go through the time-consuming uninstall and re-install process, for just changing the collation. So I chose the option to rebuild the system databases. Then change the collation as follows.
Solution:
Issued the following command with the admin priviledge:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer
/SQLSYSADMINACCOUNTS="xxx\CK" /SQLCOLLATION=Latin1_General_CI_AI
NOT WORKING! Using the Installation path, it worked:
H:\downloads\SQL Server 2008 R2 Developer Edition\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS="xxx\CK" /SQLCOLLATION=Latin1_General_CI_AS
For the 2nd Instance named Instance (notice NOT "MachineName\Instance2" or others, just Insatnace2):
H:\downloads\SQL Server 2008 R@ Developer Edition\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Instance2
/SQLSYSADMINACCOUNTS="xxx\CK" /SQLCOLLATION=Latin1_General_CI_AI
Not surprisingly, the AdventureWorks sample databases are gone, I need to restore them.
However, when I tried to re-install them, the sample databases not showing up. Deleted the mdf data files, tried again, showed some DBs, not all of them. Finally, removed the files from .../Data and .../tool/sample (created for the installation in the early process) folders, restore them again, Aha!.
Cannot connect to Report Builder in Google Chrome
It works fine in IE. But in Google Chrome, got the following error when I clicked the report builder icon. Surfed the web, no answers are found. It appears that this functionality is not supported in Google Chrome so far. Just focusing on the IE for now.
==============
PLATFORM VERSION INFO
Windows : 6.1.7601.65536 (Win32NT)
Common Language Runtime : 4.0.30319.239
System.Deployment.dll : 4.0.30319.245 (RTMGDR.030319-2400)
clr.dll : 4.0.30319.239 (RTMGDR.030319-2300)
dfdll.dll : 4.0.30319.1 (RTMRel.030319-0100)
dfshim.dll : 4.0.31106.0 (Main.031106-0000)
SOURCES
Deployment url : file:///C:/Users/CK/Downloads/ReportBuilder_3_0_0_0%20(5).application
IDENTITIES
Deployment Identity : ReportBuilder_3_0_0_0.application, Version=10.50.2500.0, Culture=neutral, PublicKeyToken=c3bce3770c238a49, processorArchitecture=x86
APPLICATION SUMMARY
* Online only application.
* Trust url parameter is set.
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of C:\Users\CK\Downloads\ReportBuilder_3_0_0_0 (5).application resulted in exception. Following failure messages were detected:
+ Downloading file:///C:/Users/CK/Downloads/RptBuilder_3/MSReportBuilder.exe.manifest did not succeed.
+ Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
+ Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
+ Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [2/20/2012 2:25:56 AM] : Activation of C:\Users\CK\Downloads\ReportBuilder_3_0_0_0 (5).application has started.
* [2/20/2012 2:25:56 AM] : Processing of deployment manifest has successfully completed.
* [2/20/2012 2:25:56 AM] : Installation of the application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [2/20/2012 2:25:56 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading file:///C:/Users/CK/Downloads/RptBuilder_3/MSReportBuilder.exe.manifest did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, IDownloadNotification notification, DownloadOptions options, Uri& appSourceUri, String& appManifestPath)
at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, Uri& appSourceUri, String& appManifestPath)
at System.Deployment.Application.ApplicationActivator.DownloadApplication(SubscriptionState subState, ActivationDescription actDesc, Int64 transactionId, TempDirectory& downloadTemp)
at System.Deployment.Application.ApplicationActivator.InstallApplication(SubscriptionState& subState, ActivationDescription actDesc)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut, String textualSubId, String deploymentProviderUrlFromExtension, BrowserSettings browserSettings, String& errorPageUrl)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
--- Inner Exception ---
System.Net.WebException
- Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
- Source: System
- Stack trace:
at System.Net.FileWebRequest.EndGetResponse(IAsyncResult asyncResult)
at System.Net.FileWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
--- Inner Exception ---
System.Net.WebException
- Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
- Source: System
- Stack trace:
at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
at System.Net.FileWebRequest.GetResponseCallback(Object state)
--- Inner Exception ---
System.IO.DirectoryNotFoundException
- Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
- Source: mscorlib
- Stack trace:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync)
at System.Net.FileWebStream..ctor(FileWebRequest request, String path, FileMode mode, FileAccess access, FileShare sharing, Int32 length, Boolean async)
at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.
==============
PLATFORM VERSION INFO
Windows : 6.1.7601.65536 (Win32NT)
Common Language Runtime : 4.0.30319.239
System.Deployment.dll : 4.0.30319.245 (RTMGDR.030319-2400)
clr.dll : 4.0.30319.239 (RTMGDR.030319-2300)
dfdll.dll : 4.0.30319.1 (RTMRel.030319-0100)
dfshim.dll : 4.0.31106.0 (Main.031106-0000)
SOURCES
Deployment url : file:///C:/Users/CK/Downloads/ReportBuilder_3_0_0_0%20(5).application
IDENTITIES
Deployment Identity : ReportBuilder_3_0_0_0.application, Version=10.50.2500.0, Culture=neutral, PublicKeyToken=c3bce3770c238a49, processorArchitecture=x86
APPLICATION SUMMARY
* Online only application.
* Trust url parameter is set.
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of C:\Users\CK\Downloads\ReportBuilder_3_0_0_0 (5).application resulted in exception. Following failure messages were detected:
+ Downloading file:///C:/Users/CK/Downloads/RptBuilder_3/MSReportBuilder.exe.manifest did not succeed.
+ Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
+ Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
+ Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [2/20/2012 2:25:56 AM] : Activation of C:\Users\CK\Downloads\ReportBuilder_3_0_0_0 (5).application has started.
* [2/20/2012 2:25:56 AM] : Processing of deployment manifest has successfully completed.
* [2/20/2012 2:25:56 AM] : Installation of the application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [2/20/2012 2:25:56 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading file:///C:/Users/CK/Downloads/RptBuilder_3/MSReportBuilder.exe.manifest did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, IDownloadNotification notification, DownloadOptions options, Uri& appSourceUri, String& appManifestPath)
at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, Uri& appSourceUri, String& appManifestPath)
at System.Deployment.Application.ApplicationActivator.DownloadApplication(SubscriptionState subState, ActivationDescription actDesc, Int64 transactionId, TempDirectory& downloadTemp)
at System.Deployment.Application.ApplicationActivator.InstallApplication(SubscriptionState& subState, ActivationDescription actDesc)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut, String textualSubId, String deploymentProviderUrlFromExtension, BrowserSettings browserSettings, String& errorPageUrl)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
--- Inner Exception ---
System.Net.WebException
- Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
- Source: System
- Stack trace:
at System.Net.FileWebRequest.EndGetResponse(IAsyncResult asyncResult)
at System.Net.FileWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
--- Inner Exception ---
System.Net.WebException
- Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
- Source: System
- Stack trace:
at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
at System.Net.FileWebRequest.GetResponseCallback(Object state)
--- Inner Exception ---
System.IO.DirectoryNotFoundException
- Could not find a part of the path 'C:\Users\CK\Downloads\RptBuilder_3\MSReportBuilder.exe.manifest'.
- Source: mscorlib
- Stack trace:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync)
at System.Net.FileWebStream..ctor(FileWebRequest request, String path, FileMode mode, FileAccess access, FileShare sharing, Int32 length, Boolean async)
at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.
Keep asking for installing .Net Framework 3.5 when trying to open Report Builder 3
Problem:
Using Windows 7 (x64), SQL 2008 R2, Report Builder 3, IE9. When I try to open Report Builder 3 in Report Manager, keep asking for installing .NET 3.5. Run it, save it and run it, and even install 4.0. Still the same messsage.
Solution:
Searched the web, found this blog, solved the problem. Basically we need to retreat to IE 8 to make it work. Crazy!
http://chanmingman.wordpress.com/2011/06/25/to-use-report-builder-you-must-install-net-framework-3-5-on-this-computer/
Using Windows 7 (x64), SQL 2008 R2, Report Builder 3, IE9. When I try to open Report Builder 3 in Report Manager, keep asking for installing .NET 3.5. Run it, save it and run it, and even install 4.0. Still the same messsage.
Solution:
Searched the web, found this blog, solved the problem. Basically we need to retreat to IE 8 to make it work. Crazy!
http://chanmingman.wordpress.com/2011/06/25/to-use-report-builder-you-must-install-net-framework-3-5-on-this-computer/
SSRS Report Manager is not supported in Google Chrome
Recently faced a blank page in Chrome when viewing a SSRS report on the report manager. Everything works fine in IE. Searched and tried in different ways, not working.
Finally, this blog suggests to use Report Server instead of Report Manager in Chrome. It worked for me.
http://bineeshthomas.wordpress.com/2011/09/16/google-chrome-issue-with-ssrs/
So Chrome does not support SSRS Report Manager at this point.
Finally, this blog suggests to use Report Server instead of Report Manager in Chrome. It worked for me.
http://bineeshthomas.wordpress.com/2011/09/16/google-chrome-issue-with-ssrs/
So Chrome does not support SSRS Report Manager at this point.
Fixing the problem in installing the Reporting Service Samples
Problem:
I downloaded the Reporting Service Samples for SQL Server 2008 R2 to a 64-bit Windows 7 machine with Visual Studio 2008. When I tried to open the solution file at:
C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\AdventureWorks 2008 R2 Offline Sample Reports
I get an error:
I downloaded the Reporting Service Samples for SQL Server 2008 R2 to a 64-bit Windows 7 machine with Visual Studio 2008. When I tried to open the solution file at:
C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\AdventureWorks 2008 R2 Offline Sample Reports
I get an error:
One or more projects in the solution could not be loaded for the following reason...
Reason:
I finally figured out it is due to the inconsistency between the project path in the solution file and the actually folder name. For some strange reason, the installation process created three folders for the offline sample:
ChartSamples 2008 R2
GaugeSamples 2008 R2
TextSamples 2008 R2
But the relative path in the solution file in XML refers to as below:
ChartSamplesRS2008R2
GaugeSamplesRS2008R2
TextSamplesRS2008R2
Solution:
The solution obviously is simply to rename the folders as specified in the solution file. In addition, for another strange reason, the rdl files for the chart samples are not loaded. So I have to manually add them into the project (total 11 files).
The solution obviously is simply to rename the folders as specified in the solution file. In addition, for another strange reason, the rdl files for the chart samples are not loaded. So I have to manually add them into the project (total 11 files).
P. S. SQL Server 2008 Samples do not have such a problem. It is only a problem for R2.
Saving SSRS reports using Report Builder 2.0
Problem 1:
Cannot save the report to the report server using Report Builder 2.0
Solution:
Run as an admin
Problem 2:
Cannot run the report or sub-report due to the credential issue, or windows login and password not accepted.
Solution:
When creating the data source, use the shared data source option as the reports are saved to report server.
Cannot save the report to the report server using Report Builder 2.0
Solution:
Run as an admin
Problem 2:
Cannot run the report or sub-report due to the credential issue, or windows login and password not accepted.
Solution:
When creating the data source, use the shared data source option as the reports are saved to report server.
Where do the columns in the Data Warehouse tables come from?
Do you know what are the sources of gender, yearly income, city, education etc. in the dim.customer table in the AdventueWorksDW2008?
Searched the OLTP, only found city in the Address table. Then used the code below and only found gender in Employee and vPersonDemographics
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name ='Gender')
The answer: They are actually in the Person.Person table in the Demographics columns implemented as XML, although they could be implemented as a separate table or columns in the same table.
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>59.46</TotalPurchaseYTD>
<DateFirstPurchase>2004-05-16Z</DateFirstPurchase>
<BirthDate>1961-06-17Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>0-25000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>1</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Graduate Degree</Education>
<Occupation>Clerical</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>0</NumberCarsOwned>
<CommuteDistance>0-1 Miles</CommuteDistance>
</IndividualSurvey>
Searched the OLTP, only found city in the Address table. Then used the code below and only found gender in Employee and vPersonDemographics
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name ='Gender')
The answer: They are actually in the Person.Person table in the Demographics columns implemented as XML, although they could be implemented as a separate table or columns in the same table.
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>59.46</TotalPurchaseYTD>
<DateFirstPurchase>2004-05-16Z</DateFirstPurchase>
<BirthDate>1961-06-17Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>0-25000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>1</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Graduate Degree</Education>
<Occupation>Clerical</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>0</NumberCarsOwned>
<CommuteDistance>0-1 Miles</CommuteDistance>
</IndividualSurvey>
Errors when installing the sampel adventureworks database for SQL Server 2012 RC0
I installed SQL Server 2012 RC0 today. Then I tried to install the sample database, using the following code:
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\AdventureWorks2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG ;
I got the access deny error. Then I changed it to the D drive. It worked. Someone says that this is because of the compressed volume issue. But both C and D are not compressed. Why???
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\AdventureWorks2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG ;
I got the access deny error. Then I changed it to the D drive. It worked. Someone says that this is because of the compressed volume issue. But both C and D are not compressed. Why???
An SSIS Problem - Package Processing hangs on forever, no error but keep processing
Problem:
Package Processing hangs on forever, no error but keep processing
Reason:
Because insert, updates, or deletes work on the same table, resulting in heap contention, the table locked for the first action, so the subsequent actions hang on forever. The SQL codes in different tasks in in the package are treated as in a batch.
Solution:
1. Change the "BulkInsertTabLock" property for the destination table to False as shown below, you may also need to increase the timeout for a large data flow as shown below.
2. Create a cluster index or PK on the destination table
An SSIS error: 64-bit and Excel-2003 conflicts
Problem:
Environment: Window 7 - 64-bit, VS 9.0 64-bit
Destination: Excel 1997-2003 file
Error when running the package:
"SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "
Solution:
Make Run64Runtime to False
Environment: Window 7 - 64-bit, VS 9.0 64-bit
Destination: Excel 1997-2003 file
Error when running the package:
"SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "
Solution:
Make Run64Runtime to False
Case Senstive for the VB code
Problem:
Execute Script task, got the error as below:
Error: 0x1 at Script: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
Reason:
Variable: DepartmentID
In the code, I used DepartmentId
Execute Script task, got the error as below:
Error: 0x1 at Script: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
Reason:
Variable: DepartmentID
In the code, I used DepartmentId
An SSIS Error on Executing a Fuzzy Lookup
Using Visual Studio 9.0 on Win 7, I got the following error:
SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0209303 at Package1, Connection manager "C:\Users\CK\Documents\SQL Server 2008 Videos from LearnItFirst.com\LearnItyFirst_SSIS2008\SSIS2008_Chapter04\04_60-LookupTransformationsVsFuzzyLookupTransformations.accdb": SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "C:\Users\CK\Documents\SQL Server 2008 Videos from LearnItFirst.com\LearnItyFirst_SSIS2008\SSIS2008_Chapter04\04_60-LookupTransformationsVsFuzzyLookupTransformations.accdb" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package1.dtsx" finished: Failure.
Changed to run64runtime to false, got the following issue:
SSIS package "04_60-LookupTransformationsVsFuzzyLookupTransformations.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "ID" (17) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Destination [316]: The processing of file "c:\Junk.txt" has started.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
SSIS package "04_60-LookupTransformationsVsFuzzyLookupTransformations.dtsx" finished: Canceled.
NO SOLUTION YET. SEE
http://connect.microsoft.com/SQLServer/feedback/details/488387/fuzzy-lookup-triggers-sqldumper-even-with-very-small-dataset-with-run64bitruntime-set-to-false
SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0209303 at Package1, Connection manager "C:\Users\CK\Documents\SQL Server 2008 Videos from LearnItFirst.com\LearnItyFirst_SSIS2008\SSIS2008_Chapter04\04_60-LookupTransformationsVsFuzzyLookupTransformations.accdb": SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "C:\Users\CK\Documents\SQL Server 2008 Videos from LearnItFirst.com\LearnItyFirst_SSIS2008\SSIS2008_Chapter04\04_60-LookupTransformationsVsFuzzyLookupTransformations.accdb" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package1.dtsx" finished: Failure.
Changed to run64runtime to false, got the following issue:
SSIS package "04_60-LookupTransformationsVsFuzzyLookupTransformations.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "ID" (17) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Destination [316]: The processing of file "c:\Junk.txt" has started.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
SSIS package "04_60-LookupTransformationsVsFuzzyLookupTransformations.dtsx" finished: Canceled.
NO SOLUTION YET. SEE
http://connect.microsoft.com/SQLServer/feedback/details/488387/fuzzy-lookup-triggers-sqldumper-even-with-very-small-dataset-with-run64bitruntime-set-to-false
A warning sign for the Lookup task, why?
I chose to redirect rows for the no-match output. That should be it for the Lookup task. Do not need to configure the ErrorOutput tab. The options for the error should be grayed out.
Additionally, be aware of using SQL Server Destination instead of OLE DB Destination for the match and no-match outputs. The OLE DB one has the option for Error Output configuration, adding confusions....
Additionally, be aware of using SQL Server Destination instead of OLE DB Destination for the match and no-match outputs. The OLE DB one has the option for Error Output configuration, adding confusions....
Data Viewer shows only a partial list of the columns
Solution: right-click ==>Data Viewer==>select Data Viewer==>Configure==>Grid==>select the columns wanted.
SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Executed a package in Win 7, got the above error.
Solution: Set the run64runtime to False for the debug option in the Property window of the solution
Solution: Set the run64runtime to False for the debug option in the Property window of the solution
An error on sending mail in SSIS usign Script Task
Using the following visual C# codes to send a hotmail mail, got the error, then changed the port number from 25 to 587 it works!
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
namespace ST_41027be1cfb14c29a9b05c97e79b0f8c.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
MailMessage msg = new MailMessage ("cxulz@hotmail.com","cxulz@hotmail.com", "This from C#","hey, buddy");
SmtpClient client =new SmtpClient ("smtp.live.com",587);
client.EnableSsl =true;
client.DeliveryMethod =SmtpDeliveryMethod.Network;
client.Credentials =new NetworkCredential("cxulz@hotmail.com","xxxxxxx1");
client.Send(msg);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
================
see the differences with the VB codes for the same functionality
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Net.Mail
_
_
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim msg As New MailMessage("cxulz@hotmail.com", "cxulz@hotmail.com", "This from VB", "hey, buddy")
Dim client As New SmtpClient("smtp.live.com", 587)
client.EnableSsl = True
client.DeliveryMethod = SmtpDeliveryMethod.Network
client.Credentials = New NetworkCredential("cxulz@hotmail.com", "nanjing1")
client.Send(msg)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
namespace ST_41027be1cfb14c29a9b05c97e79b0f8c.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
MailMessage msg = new MailMessage ("cxulz@hotmail.com","cxulz@hotmail.com", "This from C#","hey, buddy");
SmtpClient client =new SmtpClient ("smtp.live.com",587);
client.EnableSsl =true;
client.DeliveryMethod =SmtpDeliveryMethod.Network;
client.Credentials =new NetworkCredential("cxulz@hotmail.com","xxxxxxx1");
client.Send(msg);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
================
see the differences with the VB codes for the same functionality
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Net.Mail
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim msg As New MailMessage("cxulz@hotmail.com", "cxulz@hotmail.com", "This from VB", "hey, buddy")
Dim client As New SmtpClient("smtp.live.com", 587)
client.EnableSsl = True
client.DeliveryMethod = SmtpDeliveryMethod.Network
client.Credentials = New NetworkCredential("cxulz@hotmail.com", "nanjing1")
client.Send(msg)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Login Failed for User xxx
I try to mimic a video demo to set a new connection manager in SSIS, using the sa account.
Problem:
1. I enabled the sa account usin the code below:
ALTER LOGIN [sa] ENABLE
GO
2. OK in SSMS, but not working in SSIS
3. I created a new SQL login in SSMS, and changed from Windows to Mixed
4. Login as the new SQL login, still failed
5. Pipename, TCI-IP, shared all are enabled
Solution:
Restarting the SQL Server service.
Problem:
1. I enabled the sa account usin the code below:
ALTER LOGIN [sa] ENABLE
GO
2. OK in SSMS, but not working in SSIS
3. I created a new SQL login in SSMS, and changed from Windows to Mixed
4. Login as the new SQL login, still failed
5. Pipename, TCI-IP, shared all are enabled
Solution:
Restarting the SQL Server service.
Installing SQL Server 2008 R2 Sample Databases - AdventureWorks 2008R2 SR1
After I download the AdventureWorks2008R2_SR1.exe file, and try to install it,
the "Install" button is disabled by default. I have to manually create the "Samples" subdirectory for the samples as:
X:\Program Files\Microsoft SQL Server\100\Tools\Samples, where X is the drive letter.
the "Install" button is disabled by default. I have to manually create the "Samples" subdirectory for the samples as:
X:\Program Files\Microsoft SQL Server\100\Tools\Samples, where X is the drive letter.
An Example of Using Tokens in Job Steps
For background information, see https://msdn.microsoft.com/en-us/library/ms175575(v=sql.110).aspx
Step 1. make sure SQL Server Agent is set to Replace Tokens (Properties|Alert System), need to stop and re-start to take effect for the change.
Step 2. the tokenized codes cannot be parsed by SSMS, the values are replaced at runtime.
Step 3. I created a job with 2 steps: (1) back up the log when the log is 70% full, and (2) audit the log backup information in an auditing table.
---create the audit table----
CREATE TABLE MyJobLog (
ServerName SYSNAME
, Instance SYSNAME
, JobId UNIQUEIDENTIFIER
, ErrorNumber INT
, ErrorSeverity INT
, ErrorDate DATETIME DEFAULT GETDATE()
, ErrorMessage NVARCHAR(MAX)
)
GO
---Insert the following code with tokens for job step 2------
INSERT MyJobLog (
ServerName
, Instance
, JobId
, ErrorNumber
, ErrorSeverity
, ErrorMessage
,ErrorDate
)
VALUES (
'$(ESCAPE_SQUOTE(A-SVR))'
, '$(ESCAPE_SQUOTE(INST))'
, $(ESCAPE_SQUOTE(JOBID))
, $(ESCAPE_SQUOTE(A-ERR))
, $(ESCAPE_SQUOTE(A-SEV))
, '$(ESCAPE_SQUOTE(A-MSG))'
, '$(ESCAPE_SQUOTE(DATE))'
)
---Use the following code to increase the log and make sure the log backup occurs when the performance condition is met-------
DBCC SQLPERF('LOGSPACE')
WHILE 1=1
INSERT DBO.BIGTABLE VALUES('1')
---check the job history, it should have two steps succeed--------
Step 1. make sure SQL Server Agent is set to Replace Tokens (Properties|Alert System), need to stop and re-start to take effect for the change.
Step 2. the tokenized codes cannot be parsed by SSMS, the values are replaced at runtime.
Step 3. I created a job with 2 steps: (1) back up the log when the log is 70% full, and (2) audit the log backup information in an auditing table.
---create the audit table----
CREATE TABLE MyJobLog (
ServerName SYSNAME
, Instance SYSNAME
, JobId UNIQUEIDENTIFIER
, ErrorNumber INT
, ErrorSeverity INT
, ErrorDate DATETIME DEFAULT GETDATE()
, ErrorMessage NVARCHAR(MAX)
)
GO
---Insert the following code with tokens for job step 2------
INSERT MyJobLog (
ServerName
, Instance
, JobId
, ErrorNumber
, ErrorSeverity
, ErrorMessage
,ErrorDate
)
VALUES (
'$(ESCAPE_SQUOTE(A-SVR))'
, '$(ESCAPE_SQUOTE(INST))'
, $(ESCAPE_SQUOTE(JOBID))
, $(ESCAPE_SQUOTE(A-ERR))
, $(ESCAPE_SQUOTE(A-SEV))
, '$(ESCAPE_SQUOTE(A-MSG))'
, '$(ESCAPE_SQUOTE(DATE))'
)
---Use the following code to increase the log and make sure the log backup occurs when the performance condition is met-------
DBCC SQLPERF('LOGSPACE')
WHILE 1=1
INSERT DBO.BIGTABLE VALUES('1')
---check the job history, it should have two steps succeed--------
Database Mail notification to an operator
I set up an alert to back up the log when the log is 70% full, and want to notify the operator.
The job did not run in the 1st time because I did not do a full backup first.
The DB Mail part did not work either.
Solution:
1. I forgot to set the SQL Server Agent property for email communication, so enable it
2. Need to stop and re-start the SQL Server Agent!
The job did not run in the 1st time because I did not do a full backup first.
The DB Mail part did not work either.
Solution:
1. I forgot to set the SQL Server Agent property for email communication, so enable it
2. Need to stop and re-start the SQL Server Agent!
Dynamic SQL execution - EXEC @SQL vs. EXEC (@SQL)
-- EXEC @SQL vs. EXEC (@SQL)
--Without the brackets, the assumption is that the content of
the variable is a stored procedure name
--the original codes===
--find all of the indexes in the current database whose
fragmentation is over 50%
SELECT OBJECT_NAME (object_id),*
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT--object ID
I want to view
, DEFAULT -- Index ID
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
--AND
x.index_id =1
--ORDER BY
x.avg_fragmentation_in_percent DESC
GO
SET NOCOUNT ON
GO
/*Some columns can be rebuild online, others cannot such as
varchar(max) etc.
Tables with these BLOB or CLOB columns must be rebuilt
offline.
Offline rebuld is more thorough, effcient, but not always
possible
Do the online build possible to keep the business on,
Warning: temp db intensive
if not possible, have to be offline
Offline rebuilt just affects the table, not other tables in
the database.
*/
--build a temp table
DECLARE @Tables TABLE (
ID INT IDENTITY(1,1)
,
SchemaName SYSNAME
,
TableName SYSNAME
,
CanBuildOnline BIT
)
-- All tables that can be rebuilt online:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 1
FROM sys.tables t
WHERE NOT EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
--34=iamge,
35=text, 99=ntext,241=xml
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
--165=varbinary,
167=varchar, 231=sysname, -1=max
)
)
AND--if we want
to build tables with 50% and higher fragmentation
t.object_id IN (
SELECT x.object_id
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50)
-- All tables that must be rebuild offline:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 0
FROM sys.tables t
WHERE EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
)
)
DECLARE @Id INT
, @Sql NVARCHAR(1000)
,
@Schema SYSNAME
, @Table
SYSNAME
,
@CanBuildOnline BIT
SELECT @Id = MIN(ID) FROM @Tables
WHILE @Id IS NOT NULL
BEGIN
SELECT
@Schema = SchemaName
, @Table
=
TableName
,
@CanBuildOnline = CanBuildOnline
FROM
@Tables
WHERE ID=@Id
SET @Sql = 'ALTER INDEX
ALL ON [' + @Schema + '].[' + @Table
+ ']
REBUILD WITH(ONLINE
= '
+ CASE
@CanBuildOnline WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ' )'
PRINT @Sql
EXEC @Sql
DELETE
@Tables WHERE ID=@Id
SET @Id = NULL
SELECT @Id = MIN(ID) FROM
@Tables
END
--===the new codes====
--find all of the indexes in the current database whose
fragmentation is over 50%
SELECT OBJECT_NAME (object_id),*
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT--object ID
I want to view
, DEFAULT -- Index ID
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
--AND
x.index_id =1
--ORDER BY
x.avg_fragmentation_in_percent DESC
GO
SET NOCOUNT ON
GO
/*Some columns can be rebuild online, others cannot such as
varchar(max) etc.
Tables with these BLOB or CLOB columns must be rebuilt
offline.
Offline rebuld is more thorough, effcient, but not always
possible
Do the online build possible to keep the business on,
Warning: temp db intensive
if not possible, have to be offline
Offline rebuilt just affects the table, not other tables in
the database.
*/
--build a temp table
DECLARE @Tables TABLE (
ID INT IDENTITY(1,1)
,
SchemaName SYSNAME
,
TableName SYSNAME
,
CanBuildOnline BIT
)
-- All tables that can be rebuilt online:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 1
FROM sys.tables t
WHERE NOT EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
--34=iamge,
35=text, 99=ntext,241=xml
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
--165=varbinary,
167=varchar, 231=sysname, -1=max
)
)
AND--if we want
to build tables with 50% and higher fragmentation
t.object_id IN (
SELECT x.object_id
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50)
-- All tables that must be rebuild offline:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 0
FROM sys.tables t
WHERE EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
)
)
DECLARE @Id INT
, @Sql NVARCHAR(1000)
,
@Schema SYSNAME
, @Table
SYSNAME
,
@CanBuildOnline BIT
SELECT @Id = MIN(ID) FROM @Tables
WHILE @Id IS NOT NULL
BEGIN
SELECT
@Schema = SchemaName
, @Table
=
TableName
,
@CanBuildOnline = CanBuildOnline
FROM
@Tables
WHERE ID=@Id
SET @Sql = 'ALTER INDEX
ALL ON [' + @Schema + '].[' + @Table
+ ']
REBUILD WITH(ONLINE
= '
+ CASE
@CanBuildOnline WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ' )'
PRINT @Sql
EXEC (@Sql)
/*
DELETE
@Tables WHERE ID=@Id
SET @Id = NULL
SELECT @Id = MIN(ID) FROM
@Tables
*/
END
Subscribe to:
Posts
(
Atom
)