I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...
What is the work around?
Thanks in advance
JEK
I think I'm trying to do a simple query on maximum date.
I've got 100 tools that have been used over the past three years.
Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more.
Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago.
I have a list of tools and a list of times each tool was used.
I think I'm going to have to do a search that for each tool what was the times it was used. That I can do.
What I'm not sure of is how to then pull only the latest date for each tool.
Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cycdt,amt from joining two tables.
The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that,
if it is less than or equal to same months cycdt then get the same months cycdt and amt.
table1
idno,trandt
12345,04/15/2005
12345,04/22/2005
12345,07/03/2005
12345,09/10/2005
3421,03/05/2005
3421,05/06/2005
3421,07/04/2005
3421,07/15/2005
idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54
Expected result.
12345,04/15/2005,12345,04/18/2005,10.10
12345,04/22/2005,12345,05/24/2005,13.00
12345,07/03/2005,12345,07/18/2005,12.12
12345,09/10/2005,12345,09/17/2005,12.87
3421,05/06/2005,3421,05/10/2005,15.00
3421,07/04/2005,3421,07/11/2005,14.15
3421,07/15/2005,3421,08/12/2005,15.54
I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.
Thanks
I have a table with kind of data as below
PhNo    Address    DP   AutoID    Name
0123Â Â Â Â Â Â RHÂ Â Â Â Â Â Â Â Â Â 001Â Â Â 4577Â Â Â Â Â Â Â Â Â J
0123Â Â Â Â Â Â RHÂ Â Â Â Â Â Â Â Â Â 004Â Â Â 4567Â Â Â Â Â Â Â Â Â Â JD
0124 Â Â Â Â Â RHÂ Â Â Â Â Â Â Â Â Â 010Â Â Â 4467Â Â Â Â Â Â Â Â Â Â KK
0121Â Â Â Â Â Â HNÂ Â Â Â Â Â Â Â Â 007Â Â Â 4667Â Â Â Â Â Â Â Â Â RK
012Â Â Â Â Â Â Â Â Â SVNÂ Â Â Â Â Â Â Â 009Â Â Â 4787Â Â Â Â Â Â Â Â Â RKK
012Â Â Â Â Â Â Â Â Â SVNÂ Â Â Â Â Â Â Â 009Â Â Â 4787Â Â Â Â Â Â Â Â Â RKR
011Â Â Â Â Â Â Â Â Â SVNÂ Â Â Â Â Â Â Â 009Â Â Â 4787Â Â Â Â Â Â Â Â Â KKR
I need distinct phone numbers >1 for a address and if the same phNo has 2 different names I want to skip the second one.I want the output like below
PhNo    Address    DP   AutoID    Name
0123Â Â Â Â Â Â RHÂ Â Â Â Â Â Â Â Â Â 001Â Â Â 4577Â Â Â Â Â Â Â Â Â J
0124 Â Â Â Â Â RHÂ Â Â Â Â Â Â Â Â Â 010Â Â Â 4467Â Â Â Â Â Â Â Â Â Â KK
0121Â Â Â Â Â Â Â Â SVNÂ Â Â Â Â Â Â Â 009Â Â Â 4787Â Â Â Â Â Â Â Â Â RKK
012Â Â Â Â Â Â Â Â SVNÂ Â Â Â Â Â Â Â 009Â Â Â 4787Â Â Â Â Â Â Â Â Â RKR
011Â Â Â Â Â Â Â Â Â SVNÂ Â Â Â Â Â Â Â 009Â Â Â 4787Â Â Â Â Â Â Â Â Â KKR
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.Â
I am using sql server 2008.
I have been asked to analyze a DB which was under a different team. I have done the following,
1.Identified tables without PK, and clustered index
2.identified FK’s without index
3.orphan users and weak passwords
4.analyzed SPS for bad code
Is there anything else I can do? Are there any ready-made scripts which I can use.
------------------------
I think, therefore I am - Rene Descartes
Hello, friends!
sorry for the stupid question:
I created a new index on table and I'm looking to a command equal to "Analyze table ", "Compute statistics "in Oracle
to check if that index is usefull.
Thank you very much in advance.
I will be grateful if you could answer a few more questions around Analyzing Key Influencers
1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a €˜Score€™ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?
2. Can I access this €˜Score€™ and recommended inputs above programmatically?
3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?
5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the €˜Score€™? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?
6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?
Thanks bunches
MA
Excuse the elementary question; I am new to this feature.
No matter what dataset I use, I get the following error:
"The task was not able to detect any key influencers for the 'xxx' column. The values of 'xxx' seem unrelated to values of other columns."
Any ideas on what is happening here?
Here is the sample dataset:
Loan Amount
CLTV
transmitted
1,000
51.0%
1
5,000
52.0%
1
9,000
53.0%
1
13,000
54.0%
1
17,000
55.0%
1
21,000
56.0%
1
25,000
57.0%
1
29,000
58.0%
1
33,000
59.0%
1
37,000
60.0%
0
41,000
61.0%
0
45,000
62.0%
0
49,000
63.0%
0
53,000
64.0%
0
57,000
65.0%
0
61,000
66.0%
0
65,000
67.0%
0
69,000
68.0%
0
73,000
69.0%
0
77,000
70.0%
0
Thanks in advance.......
Hi,
Below is an Oracle query used for cost optimization purpose :
analyze table test estimate statistics sample 2500 Rows;
Is there any equivalent for the above in SQL Server ?
Please advice,
Thanks,
Sam
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.
Anybody nows a tool to analyze LDF files in MS SQL Server 2000?I mean, a tool that converts a LDF file in a set of SQL transactions?(similar to dbtran in sybase)thanks!
View 3 Replies View Related
Hi All
I struck up with Slow perfornace query,Please some body help me how to analyze Slow perforamnce queris.
Hi, the other day, some data was deleted by mistake, the data that we wanted to delete was in just 1 table, and we deleted the related data in a couple tables more...
We do full backups every Sunday and a Differential every day, my question is:
Is there any way to analyze the backup file to compare the backed up data with the data that the table has now, and by automatic means restore just some rows to the table, or at least see the data to insert it manually?
Thanks!
Msg 245, Level 16, State 1, Procedure CompactL1RecordsFromfirstINTRAD, Line 177
Conversion failed when converting the varchar value
'DECLARE rows_cursor CURSOR FOR
SELECT ask, dateTimed FROM iDay_Compr_GOOG
WHERE DAY (dateTimed) = ' to data type int.
I get this message while executing a stored procedure. It works "half way" but skips this statement and the whole block as a result. In the table @table_name ask is declared as float and dateTimed as DateTime. Similar statements work happily in other stored procedures with no problem. Even in the same stored procedure I have places where I use DAY (dateTimed) and they seem to work as far as I can see although it is a very branched out code with many IF ... ELSE's.
SET @SQL = 'DECLARE rows_cursor CURSOR FOR
SELECT ask, dateTimed FROM '+@table_name+'
WHERE DAY (dateTimed) = '+@day+' AND NOT ask = 0'
exec sp_sqlexec @SQL
OPEN rows_cursor
FETCH LAST FROM rows_cursor INTO @askQ, @lastTableDateTime
SET @SQL = 'UPDATE '+@table_name+' SET askSize = askSize + '+@askSize+'
WHERE dateTimed = '+@lastTableDateTime+' AND ask = '+@askQ
exec sp_sqlexec @SQL -- <== this is line 177
CLOSE rows_cursor
DEALLOCATE rows_cursor
Another puzzle for me is the line number. I used Edit-->GO TO--> 177 to single the line out and it seems to point to a different exec sp_execsql @SQL statement, the one that is down the road.
I can make neither head nor tail out of it. I am sure Jens, Cetin or Andrea or whoever stumble on this post will be able to figure this out. Anyone else's help will also be appreciated.
Many thanks.
I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.
When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?
I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?
I have been through "Getting Started" and set the AS connection to the local AS Server.
Thanks
Sutha
Edit 2007-8-9:
Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select
[FileSizeMB]=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB]=
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB]=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName]= isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
create table #TABLE_SPACE_WORK
(
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE_USED
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MBnumeric(18,4)not null,
USED_GBnumeric(18,4)not null,
AVERAGE_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null,
)
declare @fetch_status int
declare @proc varchar(200)
select@proc= rtrim(db_name())+'.dbo.sp_spaceused'
declare Cur_Cursor cursor local
for
select
TABLE_NAME=
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE= 'BASE TABLE'
order by
1
open Cur_Cursor
declare @TABLE_NAME varchar(200)
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME
select @fetch_status = @@fetch_status
if @fetch_status <> 0
begin
continue
end
truncate table #TABLE_SPACE_WORK
insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'
-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME
insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK
end --While end
close Cur_Cursor
deallocate Cur_Cursor
insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED=
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA=
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE=
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED=
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc
go
drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
CODO ERGO SUM
Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.
On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.
Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!
I want to analyze procedure cache, to find inefficient plans and parameter issues.
I do it trow DMV But my requests to DMV are very slow and demand resources because procedure cache is about several GB Actually I dont need on-line analysis.
Is it possible to have fast snapshot of procedure cache?
I have installed the excel DM addin and am trying to work through the tutorials -
When I run the 'Analyze Key Influencers' tool against the sample data through a remote AS server I get:
The task was not able to detect any key influencers for the 'Purchased Bike' column. The values of 'Purchased Bike' seem unrelated to values of other columns.
however when I run it against a local AS server I get the expected results.
I can see no differences in settings or setup between the AS instances I am trying to use - perhaps a permissions issue?
Thank you
I have a stored proc. in that proc i need to select a value based on which one is greater.
Here is a non working example
select
name,
if (truck1.age > truck2.age, truck1.age, truck2.age)
from person
left join truck truck1
on truck1.make = person.make
left join truck truck2
on truck2.make = person.make
Thanks in advance for any help with this
I'm trying to sniff or view a fairly large query that get's passed to my sql server.
The problem I'm having is that the dbcc inputbuffer only shows the first 255 chars of query and I need to see the whole thing.
Is there another method or a way to increase the size of this so I can see the whole query?
Thanks
- jeff
Hi Guys
I have a table with id, date column in it. How do i list all records which has date greater than 30 day?
Thanks
Is this Correct?
SELECT * FROM TABLE WHERE DATEDIFF(DAY, DATE, GETDATE()) > 30
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
I was comparing diffrent columns and within those columns there are specific values. I want to get the greter values using SQL 2000
i want something like these but i think there was an error in scripting
CASE
WHEN a > b,c,d,e THEN a
WHEN b > a,c,d,e THEN b
WHEN c > a,b,d,e THEN c
WHEN d > a,b,c,e THEN d
WHEN e > a,b,c,d THEN e
END
Please help. Is there any possible way to implement this? Thank you.
Hello,
I would like to create a job that runs ones a day to delete records in a table that are older than one day. Any ideas?
Thanks,
Nina Melhem
The length of my query string is greater than 255 characters. SQL Server is truncating the query string when I assign it to a Local variable.
What should i do....???
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
Is there a way to convert this? Thanks
I created this unique codes and I need all [FRMDAT] field set to "12/31/2014" in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > "12/31/2014"
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
im supposed to output the companies that have commission rates highter than company "Industrial Appparatus".
is there some whay to modify this code so that it will work?
commissionrate > ALL(Select commissionRate From salescompanydomestic Where companyName = 'Industrial Appparatus')
I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.
View 2 Replies View RelatedProblem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
What is that I am missing here ?
Hello All,
Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.
The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...
Code Snippet
=Count(Fields!ResponseTime.Value > "500")
However, this appears to just return the count of all rows and ignores the "500" part.
Am I missing something? If someone could post a alternate code snippet, that would be great.
Thanks in advance,
Clint