Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

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

 



I deployed a SharePoint feature that has an Event Receiver and ran into this error when trying to update the Today Column during a nightly batch run. The Error occured on random Document Libray items. So I did some research and found out that I needed to update the Event Receiver to run as Synchonous when Updating items. This solved the problem! By default Event Receivers run Asynchrounsly, which is not always ideal, especially in this situation.

To fix this problem just update the Element.xml file for the Event Receiver in question for the event type (Updating) you want to run as Synchronous as follows. Then redeploy. Be sure to Deactivate and Activate the site feature once it has been redeployed since it might not be used everywhere.

<Synchronization>Synchronous</Synchronization> under Elements -> Receivers -> Receiver tag

Also in my nightly batch job I used the following code to disable Events when Updating... The Using Code is further down the page.

//Disables Events when updating
using (var scope = new DisabledItemEventsScope())
{
SPList list = oWebsite.Lists["Code Rules"];
//This must be switched on, otherwise the Update method will throw an exception.
collWebsite[i].AllowUnsafeUpdates = true;

for (int k = list.Items.Count - 1; k >= 0; k--)
{
SPListItem item = list.Items[k];
//This only Works if the List Item was manually Updated before using a column called Today.
item.Properties["Today"] = DateTime.Now.Date.ToString(CultureInfo.InvariantCulture);

//SystemUpdate Updates without changing the Modified Time OR Modified By and DOES NOT make a New Version!
item.SystemUpdate(false);
}
collWebsite[i].AllowUnsafeUpdates = false;
}

        #region DisabledItemEventsScope
        /// <summary>
        /// Disabled item events scope
        /// </summary>
        /// <see cref="https://adrianhenke.wordpress.com/2010/01/29/disable-item-events-firing-during-item-update/"/>
        class DisabledItemEventsScope : SPItemEventReceiver, IDisposable
        {
            bool oldValue;

            public DisabledItemEventsScope()
            {
                this.oldValue = base.EventFiringEnabled;
                base.EventFiringEnabled = false;
            }

            #region IDisposable Members

            public void Dispose()
            {
                base.EventFiringEnabled = oldValue;
            }

            #endregion
        }
        #endregion DisabledItemEventsScope

Good Luck!



  1. Type in secpol.msc /s
  2. Select "Local Policies" in MSC snap in
  3. Select "User Rights Assignment"
  4. Right click on "Log on as batch job" and select Properties
  5. Click "Add User or Group", and include the relevant user.

 

I'm posting this as some idiot at a stupid company (you know who you are!!) changed a Global Group policy and it broke the permissions for the Service Account used to run Task Manager Jobs.

Good Luck!



1. Run regedit

2. Go to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common (repeat for muliple versions of MS Office)

3. Create a KEY called "Security" here by right clicking on the Common key and selecting New->Key and typing "Security"

4. Create a DWORD in the Security key by right clicking on Security, selecting New->DWORD and type in "DisableHyperlinkWarning"

5. Change the value of this DWORD to 1



Someone created a VM for me recently and everyime I launched IE to get to our SharePoint site internally I was being prompted to login. So I changed the IE Setting to use my credentials to log in automatically. That was OK for the time I was on the VM. BUT, when I went back in it asked me again and my settings in IE had been reverted.

This was so annoying that I Googled it and found out that it was the IE Enhanced Security Configuration that was causing this problem. So I switched it off and not it doesn't prompt me any more :-)



The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca

Calendar

<<  February 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
272812345
6789101112

View posts in large calendar

Sign in