PROBLEM: I have been struggling with this error now for days now and have tried everything I know. I have an SQL statement that pulls data from several tables into another table. The field in question is a NTEXT field from a SQL 2000 database, which I now import into a SQL 2008 R2 table that is NVARCHAR(MAX) data type because I though the issue was the NTEXT data type. However the SSIS package that is just an OLE DB Source (with 1 field) into an Excel Destination is still giving me the Unicode and Non-Unicode Error!! Several rows of data are over 8000 characters in length.

ANSWER: After a lot of pain I finally came to the conclusion that Exporting to EXCEL is not possible so I turned to CSV. I used "Flat File Destination" object, pointed to a CSV that I had created with just the Headers. The Text Qualifier was set to double quotes. In the Columns section I set the Row delimiter to {CR}{LF} and the Column delimiter to Comma{,} because it is a CSV! The final part of the puzzle was to remove and double quotes, Carriage Returns and Line Feeds. I also had to convert the NTEXT field to VARCHAR(MAX) because REPLACE will not work with NTEXT. This is what I ended up with for the columns that had these "invalid characters".

REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),[MyNTEXTColumn]), CHAR(13),' '), '"', ''), CHAR(10),'') AS 'Corrected Output'

I replaced {CR} CHAR(13) with a space to that we could have it formatted well for the consumer. I hope this helps someone out one day.

To resolve the invalid chracter from FOR XML PATH clause

Background
I have a stored procedure which returns data as an XML node. This stored procedure uses temp table to populate data. The column names in the temp table contain spaces, i.e. [Create Date].

Error
"Column name Create Date contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault."

Resolution
After removing spaces from the column names, this error was resolved.

Thanks to: http://www.codeproject.com/Tips/142869/Column-name-Create-Date-contains-an-invalid-XML-i

In this scenario, it can be many reasons to cause this problem, even the current user has enough authority to do anything on Report Manager. Please try to do trouble shooting as below:

1. Run the browser as an Administrator.
2. Add the Report Manager URL as Compatibility View settings.
3. Use http://<servername>/reports instead of using Localhost.
4. If the steps above still can't make you see the dropdown menu, it might caused by corrupt reportingservices.js file. Please try to restore this file on server.

Windows only: If you've ever come back to your PC and noticed it was rebooted, you might be curious to know exactly when it was shut down?

To figure out when your PC was last rebooted, you can simply open up Event Viewer, head into the Windows Logs -> System log, and then filter by Event ID 6006, which indicates that the event log service was shut down—one of the last things that happens before a reboot. This technique won't help you figure out when there was a power outage, but you can filter by Event ID 6005 to see when the system was last turned on—that event shows when the event log service was started again.

It's a simple tip, but could come in handy if you come back to your PC and want to figure out whether Windows Update or somebody else restarted your PC in the middle of the night, or you're just curious how many times you've rebooted in the recent past.

PROBLEM: The package contains a simple "Execute SQL Task" that has a connectiontype "EXCEL" and connection set up to a pre configured Excel connection manager. 

The task is supposed to simply create a new spreadsheet using CREATE TABLE sql statement within the task.

It seems to run fine on a colleagues computer, however when im trying to run it on mine i experience the following error: "[Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager_LPL". Connection may not be configured correctly or you may not have the right permissions on this connection."

SOLUTION: I was running the package on my computer with the Runas64Bit project property as TRUE.  Changing this to false fixed the issue.  I believe this is because the version of drivers I have for excel - ssis connectivity are 32bit. In the Project Properties->Degugging Section,  I set Run64bit RunTime to False. It started working now.

This issue that I am talking about is a very common issue that I have seen. I have, in my experience, seen many users experiencing this issue; yet, most people do not know how to troubleshoot or fix this issue. Moreover, I have seen this issue on SQL Server 2005; although not tested, I am sure you might encounter this issue in SQL Server 2008 as well.

The issue is, you right-click on the database and choose properties. Now, instead of the database properties opening up, you get the message:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[DBName]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

You do not face this issue with all databases; you can, in fact, see the properties of some of the databases and face this issue with some of the databases

One reason for this message, is obviously due to insufficient rights. But, say, you are logged in into the SQL Server using a sysadmin account, yet you face this issue. What might be the reason? Correct, the reason is that there is no owner for this database.

If you run the command “sp_helpdb Affected_Datababase_Name“, you would see that the “owner” property would show a NULL value. This issue can happen if the owner of the database is dropped from the Security Logins of the SQL Server. 

To fix the issue, run the following command against the affected database:

sp_changedbowner [ @loginame = ] ‘login’
                             [ , [ @map= ] remap_alias_flag ]

Permissions: Requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server. [From Books Online] 

Again, according to the Books Online:

• After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.


• The owner of the master, model, or tempdb system databases cannot be changed.


• To display a list of the valid login values, execute the sp_helplogins stored procedure.


• Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users that were previously assigned to dbo to this new database owner.

I hope this post is useful.

from: https://blogs.msdn.microsoft.com/suhde/2009/04/05/property-owner-is-not-available-for-database-dbname/

This trick works for a domainless environment AND when you want to connect to SQL Azure tables from you Access Web App.

The best solution is obviously to use Windows security.

If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until Access is closed:

1. copy the connect string of one of your tables
2. create a passthru queries "ptqConnect" and enter any fast SQL statement in it, like SELECT 1
3. paste the connect string of into the PTQ Connect property, and make sure you add the PWD=something; in it.
4. in the startup procedure of your app make sure you call that PTQ. Something like DCount("*", "ptqConnect") will do.

That's it. Since Access remembers opened connections until you close it, even if you close the db, your other tables will now open without any fuss, even if no password is stored in the linked tables Connect string.

If you don't want to expose the connection string that includes the PWD, you could as well initiate a connection from VBA Code and hide the code by delivering a ACCDE or just password protecting the code. The choice is all yours.

From here: http://stackoverflow.com/questions/9093266/save-password-for-odbc-connection-to-ms-sql-server-from-ms-access-2007

Private Sub cmdRefreshAllRecords_Click()
    Dim strGuidID As String
    Dim rst As DAO.Recordset
       
    ' This is NOT how to get the GUID
    ' The result will be "????????"
    strGuidID = Me.ID
   
    ' This will get the GUID. StringFromGUID is a built in MS Access function
    strGuidID = StringFromGUID(Me.ID)
   
    ' You can also get the GUID from the actual Records Field
    strGuidID = Me.Recordset.Fields("ID")
   
    Me.Requery
   
    Set rst = Me.RecordsetClone
   
    If rst.RecordCount > 0 Then
        ' Here we must convert the ID field using the StringFromGUID field.
        ' ID is the name of the field in the Recordset/Underlaying Form
        rst.FindFirst "StringFromGUID(ID) = " & strGuidID
        Me.Bookmark = rst.Bookmark
    End If
   
    Set rst = Nothing
   
End Sub

CREATE TABLE #counts ( table_name varchar(255), row_count int )

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

QUICK FIX

http://blogs.technet.com/b/the_microsoft_access_support_team_blog/archive/2016/03/09/you-may-encounter-errors-with-your-accde-mde-files-and-or-wizards-after-installing-the-march-update-for-office-2010-kb3085515.aspx