Dave's Technophorical Times

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


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

    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
    sys.tables t
    sys.indexes i ON t.OBJECT_ID = i.object_id
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    sys.allocation_units a ON p.partition_id = a.container_id
    sys.schemas s ON t.schema_id = s.schema_id
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    t.Name, s.Name, p.Rows

    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.

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

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

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

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

        SELECT TOP 1 *
        FROM    mytable
        /* You may want to add ORDER BY here */
FROM    q

Note that

FROM   mytable

will also work, but, as stated in the documentation:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Therefore, it's better to use WITH and an ORDER BY clause, which will let you specify more exactly which row you consider to be the first.

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' 

Replace Diameter with your own search.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
WHERE c.name LIKE '%Diameter%'
ORDER BY schema_name, table_name;

List ALL fields in a Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
ORDER BY schema_name, table_name;

Hope this helps.

I came across a challenge this week. Migrating Blob data from Oracle to SQL Server. I used MS Access for this since I could create an ODBC DSN to each and then have the best of ALL worlds. That is review the data, count the records and write very simple code. The link below is excellent and worked perfectly for what I needed for dealing with the Blobs.  

How To Read and Write BLOBs Using GetChunk and AppendChunk: http://support.microsoft.com/default.aspx?scid=kb;en-us;194975

The process I used for the Blobs was this:
1. Copy the data from Oracle to SQL exluding the Blobs Column.
2. Export the Blobs to File using the ID field as a key like (ID) & FileName. FileName was a column in my Oracle table so that was nice and pretty well needed so you can Identift the MIME (file type like XLS, DOC, PDF).
3. Finally Update the SQL Server table using File to Blob.

I had some other "things" in the code to deal with missing files from the Blob column but that was simple.

When attempting to Sort by different "MULTIPLE" Columns using a SQL Server Stored Procedure, this is the how it is done! 

USE AdventureWorks; 
SELECT   ContactID, 
         Title = COALESCE(Title,'')
FROM     Person.Contact
WHERE    LEFT(FirstName,1) = 'M'
           WHEN LEFT(LastName,1) = 'A' THEN RANK() OVER(ORDER BY FirstName + LastName) 
           WHEN LEFT(LastName,1) = 'M' THEN RANK() OVER(ORDER BY LastName + ', ' + FirstName, Title) 
           WHEN LEFT(LastName,1) = 'U' THEN RANK() OVER(ORDER BY LastName + ', ' + FirstName DESC) 
           ELSE                             RANK() OVER(ORDER BY LastName DESC, FirstName DESC) 

Another example: This is passing in the Sort order via a Stored Procedure Parameter. In this case @SortOrder is a varchar(100).

SELECT ItemId, ProdSize, TagNoProductIdLocation, PONo
FROM vewInvTrxSummary
        CASE WHEN @SortOrder LIKE '%Product, Tag, Location, Size, PO%' 
THEN RANK() OVER(ORDER BY Prod, TagNo, Location, Size, PONo)
                  WHEN @SortOrder LIKE '%Product, Size, Tag, Location, PO%' 
THEN RANK() OVER(ORDER BY Prod, Size, TagNo, Location, PONo)
                  WHEN @SortOrder LIKE '%Size, Product, Tag, Location, PO%' 
THEN RANK() OVER(ORDER BY Size, Prod, TagNo, Location, PONo)
                  WHEN @SortOrder LIKE '%Product, PO, Tag%'                      
                    ELSE RANK() OVER(ORDER BY Prod, TagNo, Location, Size, PONo

If you are working with SQL Stored Procedures and Functions, it's simple to add the db_owner role to your own login. However, when you want to release your database to a production environment you really want to take away the db_owner role as it has the ability to add, edit and remove database objects in a production environment. The proper way of assigning permissions is to give the user\group in question db_reader and db_writer privilages ONLY and remove the db_owner role. You then need to GRANT the user\group EXECUTE permission on all the Stored Procedures.

To do this you may need to run the following script in order to generate the script so this may be done quickly rather than applying the permission one by one. Once run, simply copy the results to a new window and Execute all lines.

DECLARE @User varchar(50)
SELECT @User = '[domain\userid]'

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0

OR if you want to GRANT to PUBLIC

DECLARE @User varchar(50)

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0

This link had more details regarding the objects you might need to query.

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


<<  March 2023  >>

View posts in large calendar

Sign in