Dear forum users,
I am a newbie in using MS SQL server with analysis services.
There seems to be no 'cross-validation' tool in MS SQL
which is frequently used in data mining and even statistics.
Is there anyone having similar difficulties?
Is there any solution like a small scripts to divide
the given dataset with multiple folds?
Your valuable comments and feedbacks would be appreciated.
Minnetongka
Hi, can someone please explain some more how to use cross-validation in SQL Server 2008 (CTP)? I read here that it "is under Accuracy Charts in Business Intelligence Development Studio, in addition to being accessible programmatically via a stored procedure call". What is the stored procedure and input parameters?
I'm actually interested in doing so inside an Integration Services package, in a data flow task.
Thanks,
Gustavo Frederico
Hi! I am using the Cross Validation tab in BIDS. Can you explain why my Liklihood Log Score is a negative number (according to BOL - meaning it is worse than a random guess) when my lift chart shoes that the alogorithm is significantly better than a random guess.
Also, the BOL definition for truenegative and falsenegative are hard for me to interpret. If I have a target state of "Yes", can you please put those definitions in yes/no terms for me?
Thanks.
Ann
How does cross-validation work in the case of models with predictable nested tables? Is it supported? For classification and regression with a flat structure, during the testing phase (that is, validation phase) of cross-validation I can think of the inputs being presented and comparing the predicted value with the real value. But in the case of nested tables, the input is not a subset of the attributes (a subset of the input vector), but whole input vectors. (For instance, complete itemsets in the case of association rules). Can you please explain some more how the validation phase works in the case of the association rules and decision trees with predictable nested tables?
thanks,
Gustavo Frederico
Also when using cross validation, which descision tree should we choose?
Thanks very much
log file rotation and cleanup script and how to remove the log file older then x days.
View 4 Replies View RelatedHi ,
I have a sample table with the following descriptions
CREATE TABLE "dbo"."tab1" (
"cola" "int" NOT NULL ,
"colb" varchar (30) NOT NULL ,
"colc" "int" NOT NULL ,
CONSTRAINT "PK___2__10" PRIMARY KEY CLUSTERED
(
"cola",
"colc"
)
)
GO
After adding some 100000 records to this table I run the sp_spaceused procedure and it give me the following information.
name - tab1
rows - 100000
reserved - 2122KB
data - 2084KB
index_size - 30KB
unused -8KB
Also i manullay calculated the number of datapages using the following equations which is available in the Sql server documentation.
Data row size = 8 ( Size of ffixed length columns) +
30 (Size of all variable length columns) +
1(Number of variable length columns ) +
7( over head)
= 46
Rows per page = (2032 -32)/46 = 43
32 - Page heade size
Number of data pages = 100000/43 = 2325 pages
= 2325 * 2 = 4650KB
Can any body tell me why there is a mismatch in result between the calculation?Am i wrong in some steps ? Is there any other way to estimate the size of table.Your valuable suggestions appreciated.
Regards
Jiji
Dear Experts,
i'm going to create a new database for the client, the initial size might be 10GB. so please guide me what will be datafile size and log file size?
i'm not creating secondary datafiles. is it ok?
Vinod
Even you learn 1%, Learn it with 100% confidence.
HiI have trouble with MSSQL2000 SP4 (without any hotfixes). During last twoweeks it start works anormally. After last optimalization (about few monthsago) it works good (fast, without blocks). Its buffer cache hit ratio wasabout 99.7-99.8. Last day it starts work slow, there was many blocks anddedlocks. There are no any queries, jobs and applications was added. Nowbuffer cache hit ratio oscilate about 95-98. I try update statistics andreindex some hard used tables, but there is no effect or effect is weryshort (after few hours problem return).Mayby somene know what it could be?Is it possible to estimate how each table (using DBCC SHOW_STATISTICS orDBCC SHOWCONTIG or others) how the table affect on total buffer cache hitratio?Marek---www.programowanieobiektowe.pl
View 1 Replies View RelatedHas anyone come across a more accurate method than sp_spaceused to estimate the size of a full database backup for SQL Server 2000 ?
I have found this to have too great a variance (even after running updateusage) to rely on any accuracy for it. I have also looked at perhaps using the ALLOCATED Pages indicated in the GAM pages but this also seems to be pretty inaccurate.
I have a number of servers where space can be limited and backups using Maintenance Plans have occasionally failed because they delete the old backups AFTER they do the latest one. I am writing a script which can check the space remaining and adjust the backup accordingly but the variance I have observed so far with sp_spaceused is too great.
Any ideas welcomed.
Hello all,
The following link contains the DDL and data for a sample table (RF.sql): Click the Download link at the up right hand corner to save the file RF.sql.
http://www.freedrive.com/file/6c75af1b54121e4804210955262ec032
I am working in Sql server 2005 sp2.
The table describes the tire and also the history of movement of the tire from one tire_position to another on same or a different truck. I need to capture the rotation of the tire. Rotation can be captured from changes in tire_position or changes in Truck_ID columns.
Rotation frequency is 0 if both tire_position and truck_ID stay thesame till the tire is scrapped (disposition_to = C )
If only one of them changes, rotation frequency is count the number of times it changes
If both change, rotation frequency is the maximum of the count of the two. So if truck_ID changes 2 times and tire_position changes 3 times. The rotation frequency is 3.
Obviously, if both change same number of times, rotation frequency can be coun anyone of them.
So I need to have a new column added to this table for the rotation frequency.
Checking them manually, the rotation frequency for the each serial_number is: 2361 is 4, 4853 is 3, 3007 is 1, 2743 is 2, 3949 is 2, 3995 is 2, 5666 is 1.
Please let me know If something is not clear.
Thanks
Hello,
I have a SQL Server that generates a reasonable amount of log data in the SQL Event logs. I'm finding it difficult to use this data as the individual log files become rather large i.e. several hundred MB before they rotate.
What I would like to achieve: Set a constraint like: Each log will rotate when it reaches [XX]Mb in size and the files will be kept through [Y] rotations.
I can't seem to find how to do this through Enterprise Manager. I've had a decent look through google and there seems to be no info on this forum....can anyone point me in the right direction?
Thanks for your time.
Rgds,
Sithlordelvis.
Is it possible to align a text box to display text with rotation? For example can I display it rotated 90 degrees left so that it reads from the bottom towards the top of the page? All I can find is left/right alignment and top/middle/bottom alignment. I want to rotate the text.
Thanks.
Hello I was very happy to have found the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491642&SiteID=1 that explained how to get the text to display Bottom-to-Top/Left-to-Right.
The solution was to setup a function that creates a bitmap the text to be displayed.
This works well and correctly displays the text image in HTML and PDF. (Excel, XML and CVS won't export backgroud images).
To extend the solution to wrap text it requires a few additional lines...
Code Snippet
Function LoadImage3(ByVal stText As String)
stText = stText.PadRight(10)
stText = stText.PadLeft(10)
Dim iMaxLength as Integer = 180
Dim iMaxWidth as Integer = 180
Dim f As Drawing.Font = New Drawing.Font("Arial",7, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point)
Dim bmpImage As New Drawing.Bitmap(1, 1)
Dim MyGraphics As Drawing.Graphics = Drawing.Graphics.FromImage(bmpImage)
Dim imageSize As Drawing.SizeF = MyGraphics.MeasureString(stText, f)
Dim i As New System.Drawing.Bitmap(iMaxWidth, iMaxLength)
Dim g As Drawing.Graphics = Drawing.Graphics.FromImage(i)
Dim rectF1 As New Drawing.Rectangle(-(iMaxWidth/2),-(iMaxLength/2),iMaxWidth,iMaxLength )
g.FillRectangle(Drawing.Brushes.White, 0, 0, i.Width, i.Height)
g.TranslateTransform((iMaxWidth/2), (iMaxLength/2) )
g.RotateTransform(270.0F) 'flip the image 270 degrees
g.DrawString(stText, f,Drawing.Brushes.Black, rectF1)
g.DrawRectangle(New Drawing.Pen(Drawing.Color.White, 1), rectF1)
g.Flush()
Dim stream As IO.MemoryStream = New IO.MemoryStream
Dim bitmapBytes As Byte()
i.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg) 'Create bitmap
bitmapBytes = stream.ToArray
stream.Close()
i.Dispose()
Return bitmapBytes
End Function
Items highlighted in yellow reflect changes made to orignal solution.
Hope this helps!!
Hey all,
Quick question regarding validation!
I have a table of contact details containing the usual name, adresss etc fields.
I want to validate the fields Country and telephone together so that fopr example if country = 'UNITED KINGDOM' the telephone has to begin with +44 if it doesnt i want it to add the +44!
I can do this through writing a little program but just wanted to explore the possibility of doing this with SQL or functions already available with MSSQL Server
cheers
ed
Hi All:
When I check out > 2000 customer databases cross 8 SQL 2000 servers, I wish I could use
"Select * from " + @server + "." + @databaseName + ".information_Schema.coulmns
where column_name = " + @column_name.
It did not works in this way.
However, why it works on
"Select * from serverA.master.information_schema.columns"?
It is also a cross server query but is on Master DB.
thanks
-D
Hi There,
I have a software where the frontend is created in VB and backend is created in SQL server. Our HO sends a file, this file has only one month validity. It has 2 files one is parent and one is child file. while uploading the file using software it creates the following files
Clipboard Clip File, Cursor File, DBE file, EXM, GLB file, MOD File, SQL server subscription file, MTB file, PAT file, PSK file, PTD file, QCM file, QTY file, SEM file, SMO file,STR file, TOP file and a VLP file.This files uploads questions and answers in database. and in SQL Profiler it runs the following queries
select dbname from master ..syslogins where loginname =UPPER ( 'S' )
select convert ( varchar ( 10 ) , getdate ( ) , 105 ) from sysusers where uid =0
select convert ( varchar ( 10 ) , getdate ( ) , 105 ) + ' ' + convert ( varchar ( 8 ) , getdate ( ) , 108 ) from sysusers where uid =0
select convert ( varchar ( 10 ) , getdate ( ) , 120 ) from sysusers where uid =0
All of them returns date. But after this it throws error file expired. I dont understand from where it checks date. and which file contains questions and answers
Can anyone please tell me which file might contain date validation and how do i remove it. I can mail the files for your kind reference.
Thanking you and waiting for some kind replies
mystical
Hi all,
My support team inform me that my servers have been updated and rebooted on monday. Now I need to run some cross db queries today and I dont seem to be able to connect anymore. I am getting the (dreaded apparant but not surprisingly) Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Now first thing I did was try to drop both linked servers and add again. I am thinking that the particular server has a dynamic ip which I have determined. Ive noticed a work around on MSFT but it say about using a connection string with IP.
Can anyone help me work this out? id appreciate it.
"Impossible is Nothing"
Hi.
I have one question regarding SQL Server replication.
I have setup SQLServer merge replication at my work, one of our subscriptions has unreliable connection to the publisher, after setting up the replication to this subscriber, it happened once that the connection was really bad so the merge agent for this subscription was failing before synchronizing data changes, and after that , the connection went good & the merge agent completed the synchronization successfully.
After a while I decided to validate all subscriptions, I discovered that this subscription has different row count for many articles although the merge agent was finishing all synchronizations successfully!!
so my question is:
how could this happen? I mean finishing the sync with success and still have different records!!
I thought that SQL Server will keep make the sync fail just because it can't do it!!
Is it right or there is something I misunderstood here?
How can I prevent this from happening again? at least I want SQL Server to inform me by failing the merge agent synchronizations.
I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.
How would I create a cross-tab query in SQL Server?
Table for input to cross-tab query: [Inspection Data]
SerialNumber ParamName Result Pass/Fail
001 Test1 3.43 Pass
001 Test2 3.27 Pass
001 Test3 2.97 Pass
002 Test1 2.88 Pass
002 Test2 3.01 Pass
002 Test3 4.22 Fail
003 Test1 3.11 Pass
003 Test2 2.91 Pass
003 Test3 3.28 Pass
Cross-tab Query Output:
Test1 Test2 Test3 Pass/Fail
001 3.43 3.27 2.97 Pass
002 2.88 3.01 4.22 Fail
003 3.11 2.91 3.28 Pass
Thanks,
Sam
I want a query which is used in sql server like access query
TRANSFORM Sum(Q_DayBook.Debit) AS SumOfDebit
SELECT Q_DayBook.Purticular, Sum(Q_DayBook.Debit) AS [Total Of Debit]
FROM Q_DayBook
GROUP BY Q_DayBook.Purticular
PIVOT Q_DayBook.CDate;
HiI'm rather new to ASP.NET (was working with win forms before) and having a few problems with my first app. To the point - this is an internal system - every week about 50 branches send financial reports to the headquaters where those excel files are validated against data in sql server. after file is uploaded to the server I'm opening it using Excel object model and iterating through it line by line. Apart from format and datatype checking, for each line there are ~2 stored procedures invokedEverything is working actually but my concern is performance.. After about 800 rows it is getting really slow.. Appreciate any comments on this.thanksAM
View 1 Replies View RelatedI am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
I am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
hi, here I create a cross tab query in access 97 this is the code
TRANSFORM Count([1].ORD_NBR) AS [The Value]
SELECT [1].SRMGR_NAME, Count([1].ORD_NBR) AS [Total Of ORD_NBR]
FROM 1
GROUP BY [1].SRMGR_NAME
PIVOT [1].ASR_SUPP;
I export this table to sqlerver 6.5 and run the same code. IT DIDNOT WORK
My question is how can I implement crosstab query in sql server 6.5?
any one can help I really do appreciate
Ali
Hi,
Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?
Thanks!
The system has worked for two years.SQL Server 2000 runs on Windows 2000 Server "A".Another instance of SQL Server 2000 was moved from Windows 2000 Server"B" old to Windows 2003 Server "B" new, by restoring a backedup copy from old to new.The system has cross server updates, where Server "A" is updatingrecords in very large tables on Server "B".After the move from Server "B" old to Server "B" new, suddenlythe cross server updates fail, but only when they are conducted withina Begin and Commit Transaction batch.The system works when I remove the Begin and Commit Transaction, and nolonger have them run in a batch.Also, I had a cross server update, having "A" update records in"B", from a delete trigger residing on "A" table. Deletesagainst the "A" table fail so long as the trigger remains. Once Iremove the delete trigger from the "A" table, I can now deleterecords on the "A" table.Thanks. Dotnet Fellow
View 9 Replies View RelatedIs there some way to set up management studio to where a specific saved script (.sql file) won't run unless it's on a server in a certain domain (or at least on a specific server)? I know can store @@servername in a variable and then run the script inside of an IF block only if it's a certain server name, but I'm looking for something a little more bulletproof. I'll probably end up putting the script in a .NET app where I can control the connection string, but I'm wondering if there's a T-SQL alternative to this.
Thanks,
-Dave
The system has worked for two years.
SQL Server 2000 runs on Windows 2000 Server €œA€?.
Another instance of SQL Server 2000 was moved from Windows 2000 Server €œB€? old to Windows 2003 Server €œB€? new, by restoring a backed up copy from old to new.
The system has cross server updates, where Server €œA€? is updating records in very large tables on Server €œB€?.
After the move from Server €œB€? old to Server €œB€? new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch.
The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch.
Also, I had a cross server update, having €œA€? update records in €œB€?, from a delete trigger residing on €œA€? table. Deletes against the €œA€? table fail so long as the trigger remains. Once I remove the delete trigger from the €œA€? table, I can now delete records on the €œA€? table.
Thanks. Dotnet Fellow
I need to update serverB.databaseB.tableB.columnB value
based on serverA.databaseA.tableA.columnA value change,
the update trigger in serverA.tableA works fine in
updating a testing databaseB.tableB.columnB in the same serverA.
serverB is as the linked server in ServerA and DTC is on.
when change the trigger to point to serverB.databaseB....
error:--------------------------------------------
Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
thanks for the help
David
I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?
View 1 Replies View RelatedI was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
I have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.