https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database 

Is there an easy way to determine how much space on disk each table is consuming?

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

From here: https://support.microsoft.com/en-ca/help/214459/info-optional-feature-not-implemented-error-message

I got this error when running the code via MS Access VBA.

When you use ActiveX Data Objects (ADO) to pass parameters to a stored procedure, you may receive the following error:

This error can occur if you attempt to set the TYPE of a parameter in an ADODB command object's parameters collection to a type that is not supported by the data provider.

For example, using SQL Server 7.0, create a stored procedure on the PUBS database:

   CREATE PROCEDURE GetEmployeeInfo (@thedate datetime, @NumEmployees int output)AS 
       SELECT @NumEmployees =  count(*) FROM EMPLOYEE WHERE hire_date < @thedate
   GO

This stored procedure returns an output parameter of type int indicating the number of employees hired before a given date. The date is passed to the stored procedure as a parameter, and the number of employees is passed to the calling program as an output parameter.

Now create an ADO application to use the stored procedure. The example given is written in Visual Basic.

Private Sub MySubroutine()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command

Set dbConnection = New ADODB.Connection
Set dbCommand = New ADODB.Command

Dim DSNNAME As String
Dim USERNAME As String
Dim PASSWORD As String

DSNNAME = "Pubs"
USERNAME = "sa"
PASSWORD = ""

dbConnection.Open DSNNAME, USERNAME, PASSWORD
dbCommand.ActiveConnection = dbConnection

Dim TheDate As Date
TheDate = Now

dbCommand.CommandText = "GetEmployeeInfo"
dbCommand.CommandType = adCmdStoredProc
dbCommand.Parameters.Append dbCommand.CreateParameter("@thedate", adDBDate, adParamInput, 0, TheDate)
dbCommand.Parameters.Append dbCommand.CreateParameter("@NumEmployees", adInteger, adParamOutput, 0)
dbCommand.Execute

Dim strTheString As String
strTheString = "There are " & dbCommand.Parameters("@numemployees") & " employees who were hired before " & TheDate
MsgBox strTheString, vbOKOnly, "Demonstration"
End Sub

When the sample code is run, it gives this error:

This is because SQL Server does not support the adDBDate datatype. To correct this problem, change the datatype of the @theDate parameter to adDBTimeStamp.

In order to determine the number, names, types, and sizes of parameters needed in a stored procedure, use the Parameters.Refresh method of the command object. You can call this method during development of your application to determine the correct requirements for the stored procedure, then remove the expensive call to Parameters.Refresh after you have gathered the necessary data.

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
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.

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    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'
 )
RETURNS VARCHAR(1000)
AS 
 BEGIN
  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,
       DummyID INT IDENTITY(1,1) NOT NULL
  )

  INSERT INTO @TempTable  
  SELECT DISTINCT
      pm.PipelineName,
      pct.ProductCarriedName,
      pct.ProductCarriedNameFrench,
      pspc.SUBABC,
      pspc.PercentH2S,
      pspc.GIS_ProductCarriedTypeId,
      ps.GIS_PipelineMasterId
    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,
      pspc.SUBABC;

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

  WHILE(@count <= @count_max)
  BEGIN
   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
  END
  
  IF @EnglishOrFrench = 'English'
  BEGIN
   SET @ReturnEnglish = CASE @ReturnEnglish
                        WHEN NULL THEN NULL
                        ELSE (CASE LEN(@ReturnEnglish)
                              WHEN 0 THEN @ReturnEnglish
                              ELSE LEFT(@ReturnEnglish, LEN(@ReturnEnglish) - 1)
                              END )
                        END
 
   RETURN @ReturnEnglish
  END
  ELSE
  BEGIN
   SET @ReturnFrench = CASE @ReturnFrench
                       WHEN NULL THEN NULL
                       ELSE (CASE LEN(@ReturnFrench)
                             WHEN 0 THEN @ReturnFrench
                             ELSE LEFT(@ReturnFrench, LEN(@ReturnFrench) - 1)
                             END )
                       END

   RETURN @ReturnFrench
  END   
  
  RETURN @ReturnEnglish  
   
 END

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' 

http://msdn.microsoft.com/en-us/library/ms187821.aspx

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.