Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access
    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
DROP TABLE #counts


The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
    dbid > 0
    dbid, loginame

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

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

ALTER FUNCTION [dbo].[fnGetPipelineProductsCarried]
  @GIS_PipelineMasterId INT,
  @EnglishOrFrench NVARCHAR(20) = 'English'
  DECLARE @ReturnEnglish VARCHAR(1000) = ''
  DECLARE @ReturnFrench VARCHAR(1000) = ''
  DECLARE @ProductCarriedName NVARCHAR(255) = ''
  DECLARE @ProductCarriedNameFrench  NVARCHAR(255) = ''
  DECLARE @TempTable TABLE (
          PipelineName NVARCHAR(255),
          ProductCarriedName NVARCHAR(255),
          ProductCarriedNameFrench NVARCHAR(255),
          SUBABC NVARCHAR(5),
          PercentH2S DECIMAL(18,3),
       GIS_ProductCarriedTypeId INT,
       GIS_PipelineMasterId INT,

  INSERT INTO @TempTable  
    FROM ((GIS_PipelineSegment ps
   INNER JOIN GIS_PipelineSegmentProductCarried pspc ON ps.GIS_PipelineSegmentId = pspc.GIS_PipelineSegmentId)
   INNER JOIN GIS_PipelineMaster pm ON ps.GIS_PipelineMasterId = pm.GIS_PipelineMasterId)
   INNER JOIN GIS_ProductCarriedType pct ON pspc.GIS_ProductCarriedTypeId = pct.GIS_ProductCarriedTypeId
   WHERE pm.GIS_PipelineMasterId = @GIS_PipelineMasterId
   ORDER BY pm.PipelineName,

  DECLARE @count INT
  DECLARE @count_max INT
  SET @count = 1
  SELECT @count_max = COUNT(DummyID) FROM @TempTable

  WHILE(@count <= @count_max)
   SELECT @ProductCarriedName = ProductCarriedName,
             @ProductCarriedNameFrench = ProductCarriedNameFrench
     FROM @TempTable
    WHERE DummyID = @count
   SET @ReturnEnglish = @ReturnEnglish + @ProductCarriedName + ', '
   SET @ReturnFrench =  @ReturnFrench  + @ProductCarriedNameFrench + ', '
   --Must Always be at the end of the Loop
   SET @count = @count + 1
  IF @EnglishOrFrench = 'English'
   SET @ReturnEnglish = CASE @ReturnEnglish
                        WHEN NULL THEN NULL
                        ELSE (CASE LEN(@ReturnEnglish)
                              WHEN 0 THEN @ReturnEnglish
                              ELSE LEFT(@ReturnEnglish, LEN(@ReturnEnglish) - 1)
                              END )
   RETURN @ReturnEnglish
   SET @ReturnFrench = CASE @ReturnFrench
                       WHEN NULL THEN NULL
                       ELSE (CASE LEN(@ReturnFrench)
                             WHEN 0 THEN @ReturnFrench
                             ELSE LEFT(@ReturnFrench, LEN(@ReturnFrench) - 1)
                             END )

   RETURN @ReturnFrench
  RETURN @ReturnEnglish  

The Actual Error for me was

Msg 7347, Level 16, State 1, Line 2

OLE DB provider 'SQLNCLI10' for linked server 'Common_Pts' returned data that does not match expected data length for column '[Common_Pts].[Pts].[dbo].[veRegulatoryInstrument].prefix'. The (maximum) expected data length is 4, while the returned data length is 5. 

After creating a linked server in ms sql express. and running the view I got the following error. to solve the problem run sp_refreshview command from the server that is hosting the view.

sp_refreshview [ @viewname = ] 'viewname' 

This is so simple compared with the older versions.

To remove an old connection that you don't want from the dropdown list you simply click the dropdown list, use your up down keys to highlight the one you want to remove and then hit the delete key! GONE! No need to go find a configuration fiile like you used to in the older versions. 

I had an issue recently where I was not able to remove an instance of SQl Server, or so I thought. I kept on trying to remove the instance via Microsoft SQL Server 2014 RATHER THAN Microsoft SQL Server 2014 (64-bit). Once I selected the 64 bit option in Add-Remove Features I was able to finally gett rid of the instance I didn't want! Pretty dumb of me to not see the 64 bit option right away but there you go, sometimes you can't see the forest for the trees. 

This error usually happens if you have two SQL Server instances on the same box and someone removed one at some point.


To work around this problem, open a command prompt, type the following command, and then press ENTER:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

 Collapse this tableExpand this table
Microsoft SQL Server 2012 110
Microsoft SQL Server 2008 R2 100
Microsoft SQL Server 2008 100
Microsoft SQL Server 2005 90

After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.

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


<<  February 2017  >>

View posts in large calendar

Sign in