SQL Server 2012 :: How To Determine Metadata On DBCC Results
May 26, 2015
I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.
I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.
I tried
SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')
but I got back
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.
View 1 Replies
ADVERTISEMENT
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Aug 22, 2014
While starting querying with sql server mdx query i m getting error loading metadata:no cubes found.
View 6 Replies
View Related
Oct 1, 2014
I am tasked with truncating and reloading tables from one server to another. Company policy prevents cross-server queries, but allows SSIS packages with cross-server connections. I am doing this for about 25 tables. I have the table names in a single table & I have created an FEL to execute tasks against each table one-by-one. It works fine to truncate all the tables. I run into issues, though, with the DataFlowTask. I'm able to tell it which server & table to dynamically connect from and to, but it doesn't know how to map the metadata. They're the exact same columns and field names in both source & destination.
View 9 Replies
View Related
Dec 4, 2014
I must determine FILEGROUP on my DB in SQL Server. Because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies
View Related
Dec 4, 2014
i must determin FILEGROUP on my DB in SQL Server. because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies
View Related
Oct 6, 2015
How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.
View 2 Replies
View Related
Aug 14, 2014
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
View 3 Replies
View Related
Mar 18, 2014
I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE
I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
When I try to create this proc, I get the following error:
Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.
Ok, so I can't EXECUTE AS sa...
View 5 Replies
View Related
Oct 4, 2005
Hello Ryan.
I run DBCC PAGE (dbname, 1, 136, 3) with trace 3604 on:
Server: Msg 8968, Level 16, State 1, Line 1
Table error: DBCC PAGE page (1:136) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 10 Replies
View Related
Nov 15, 2007
Dear Experts,
just now i've read a great article from sql server performance.com
and understand several key factors related to performance.
still i have one doubt that what will be the best ratio between
extents scanned and extent switches.
here i'm pasting some of my table result. please suggest me how is the performance of the table
table 1
- Pages Scanned................................: 85618
- Extents Scanned..............................: 11283
- Extent Switches..............................: 85405
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 12.53% [10703:85406]
- Logical Scan Fragmentation ..................: 49.93%
- Extent Scan Fragmentation ...................: 46.31%
- Avg. Bytes Free per Page.....................: 2707.1
- Avg. Page Density (full).....................: 66.55%
table2
- Pages Scanned................................: 60648
- Extents Scanned..............................: 7650
- Extent Switches..............................: 60639
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.50% [7581:60640]
- Logical Scan Fragmentation ..................: 50.93%
- Extent Scan Fragmentation ...................: 64.01%
- Avg. Bytes Free per Page.....................: 2672.5
- Avg. Page Density (full).....................: 66.98%
thankyou very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
May 30, 2001
One of our databases seems to be looking dodgy as some scheduled jobs are failing, but DBCC CHECKDB is no use since it has been running for over 1/2 hour without giving me any results, just the spinning globe.
How do I find out what is wrong without resorting to backups?
View 3 Replies
View Related
Jul 28, 2004
I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.
First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?
Clive
View 12 Replies
View Related
Aug 9, 2004
Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?
Clive
View 1 Replies
View Related
May 21, 2008
Dear All,
i've used the DBCC showcontig command against my table table103
but i dont know how to analyze the results of fragmentation levels. please give me some explanations or some good links.....
the results are:
DBCC SHOWCONTIG scanning 'TABLE103' table...
Table: 'TABLE103' (1899257921); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 13
- Extent Switches..............................: 18
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 15.79% [3:19]
- Logical Scan Fragmentation ..................: 90.00%
- Extent Scan Fragmentation ...................: 92.31%
- Avg. Bytes Free per Page.....................: 3281.4
- Avg. Page Density (full).....................: 59.46%
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 10 Replies
View Related
May 10, 2007
Does anyone know if there is a simple way to get the results of a DBCCINPUTBUFFER() request into a table? I have a process for monitoringactivity that will give me the results of sp_who2 into a temp table,and want to scroll through the active connections and get the inputbuffers into another table for review:Insert into #TmpWhoexec sp_who2 'active'Something like that with the dbcc command.I am using SQL 2000 SP4.Thanks,Tom
View 3 Replies
View Related
Feb 28, 2008
So, basically I'm trying to do an insert into exec(dbcc shrinkdatabase)
Code Snippet
DECLARE
@SQL VARCHAR(1024)
,@DBName VARCHAR(512)
SET @DBName= 'admin'
IF OBJECT_ID('tempdb.dbo.#ShrinkDB') IS NOT NULL
DROP TABLE #ShrinkDB
CREATE TABLE #ShrinkDB
(
DbId INT
,FileID INT
,CurrentSize BIGINT
,MinimumSize BIGINT
,UsedPages BIGINT
,EstimatedPages BIGINT
)
SET @SQL=
'
INSERT INTO #ShrinkDB
(DbId,FileId,CurrentSize,MinimumSize,Usedpages,EstimatedPages)
EXEC(''DBCC SHRINKDATABASE(' + @DBName + ')'')
'
EXEC(@SQL)
SELECT * FROM #ShrinkDB
and receive the following:
Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement.
I've tried adding a begin tran and commit tran around it, doesn't help ...
Is there any way around this? Is there any other way to capture the output of a shrink database from a procedure perspective?
Thanks
View 4 Replies
View Related
May 23, 2008
Guys,
I need to send a group of people a list of specific processes running on the server, one of the requirements is to send them what's actually being ran on the machine. I have the information I was on the sysprocess tabke and the results of the DBCC Inputbuffer. Is there a way to link both result sets?
This is the criteria of the processes that neeed to be sent out to my users:
SELECT *
FROM master.dbo.sysprocesses p
WHERE last_batch < DATEADD(mi, -5, GETDATE())
AND dbo.fncGetNumLocks(p.spid, DB_ID('EngDataMart')) > 1
GROUP BY p.spid, p.loginame, p.hostname, dbo.fncGetNumLocks(p.spid, db_id('DBName'))
ORDER BY p.spid
Thanks,
View 1 Replies
View Related
Jul 20, 2005
Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.
View 2 Replies
View Related
Feb 19, 2008
Hi:
how could I load "DBCC showcontig (@TableName) with tableResults" to a table?
I created a table and let insert to it via above "DBCC...."
but problem is the results with "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", or how could I mute this output line for each table ?
thanks
David
--=========================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -------------------- -------------------- ----------------- ----------------- ---------------------- -------------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------- -------------------- -------------------- ---------------------- ----------------------
sysproxylogin 37575172 clust 1 0 0 0 0 0 0 0 0 0 0 0 100 0 0 0 0
View 1 Replies
View Related
Mar 13, 2014
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM
[code]...
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
View 9 Replies
View Related
Aug 7, 2014
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
View 2 Replies
View Related
Sep 11, 2014
I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:
create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)
[Code].....
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas
View 7 Replies
View Related
Jul 9, 2015
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
View 9 Replies
View Related
Aug 25, 2015
Is there a DMV or similar in SQL 2012, or SQL 2008, that shows when a statistic was last used by the optimizer? I would like to cleanup some of the auto-generated stats, assuming it's possible to do so. In particular I'm looking to drop those statistics that were created by one-off queries, data loads, etc, and are now doing nothing but adding to the execution time of Update Statistics jobs.
View 6 Replies
View Related
Sep 17, 2015
I am working on Sql Server 2012. and I have multiple databases there. Out of those, i want to move one of my databases to other SQL server 2012, For that i was trying to get approximate size of my database on current server. As i don't have the admin rights, so i can't get that. Can i get the approximate size by right clicking on database and by using the size property Under Database category to get the size idea?
View 4 Replies
View Related
Jun 5, 2014
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
View 9 Replies
View Related
Sep 5, 2014
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
View 8 Replies
View Related
Sep 29, 2015
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
[Code] ....
View 9 Replies
View Related
Nov 2, 2015
I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"
What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.
I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")
SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())
View 6 Replies
View Related
Oct 20, 2014
Is there a way to determine when the SSAS Service last restarted? preferably a [mdx] query like you can do with the DB engine using a T-SQL query but any method that does not going to the servers OS and checking would be fine.
View 1 Replies
View Related
Nov 4, 2015
code that I can use to determine if a certain table has been accessed in the past?
View 6 Replies
View Related
Nov 25, 2014
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA
SET Supervisor = 'A123'
WHERE PersonnelNumber = 'B456'"
View 4 Replies
View Related
