Do SQL Server's developers even know what the datetime datatype is?

Just a bit of a rant here...
I've marveled for a long time that SQL Server developers chose to store
SQL Agent job history dates in in the sysjobhistory table as two
seperate columns in integer format instead of a single datetime column.
I mean come on.
Then today I discovered that the Windows 2003 Perfmon stores dates as
char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
Char (24) won't even do an explicit conversion to datetime. You have to
only grab the first 23 characters.
I think Bill G. needs to send around an internal memo reminding
everyone about the datetime data type.
/end rantI wish Gert had included some background on why this decision came about,
but otherwise a great resource:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm
As for Perfmon, isn't it possible it was formatted to CHAR(24) because it
usually logs to a file, and the file needs to be understood by more systems
than just SQL Server?
<pshroads@.gmail.com> wrote in message
news:1164659681.627479.274240@.h54g2000cwb.googlegroups.com...
> Just a bit of a rant here...
> I've marveled for a long time that SQL Server developers chose to store
> SQL Agent job history dates in in the sysjobhistory table as two
> seperate columns in integer format instead of a single datetime column.
> I mean come on.
> Then today I discovered that the Windows 2003 Perfmon stores dates as
> char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
> Char (24) won't even do an explicit conversion to datetime. You have to
> only grab the first 23 characters.
> I think Bill G. needs to send around an internal memo reminding
> everyone about the datetime data type.
> /end rant
>

Do SQL Server's developers even know what the datetime datatype is?

Just a bit of a rant here...
I've marveled for a long time that SQL Server developers chose to store
SQL Agent job history dates in in the sysjobhistory table as two
seperate columns in integer format instead of a single datetime column.
I mean come on.
Then today I discovered that the Windows 2003 Perfmon stores dates as
char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
Char (24) won't even do an explicit conversion to datetime. You have to
only grab the first 23 characters.
I think Bill G. needs to send around an internal memo reminding
everyone about the datetime data type.
/end rantI wish Gert had included some background on why this decision came about,
but otherwise a great resource:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm
As for Perfmon, isn't it possible it was formatted to CHAR(24) because it
usually logs to a file, and the file needs to be understood by more systems
than just SQL Server?
<pshroads@.gmail.com> wrote in message
news:1164659681.627479.274240@.h54g2000cwb.googlegroups.com...
> Just a bit of a rant here...
> I've marveled for a long time that SQL Server developers chose to store
> SQL Agent job history dates in in the sysjobhistory table as two
> seperate columns in integer format instead of a single datetime column.
> I mean come on.
> Then today I discovered that the Windows 2003 Perfmon stores dates as
> char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
> Char (24) won't even do an explicit conversion to datetime. You have to
> only grab the first 23 characters.
> I think Bill G. needs to send around an internal memo reminding
> everyone about the datetime data type.
> /end rant
>

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

do I need to pay for support cases to help MS fix bugs?

I've filed a few bugs on connect.microsoft.com, and I am being asked for 2nd time to open a support case to make it easier to work on the bug.

The reason is that the bug needs to be reproduced, and working with a support person will help MS setup the same database, cube, etc.

However, I am wondering why am I as a customer required to pay for the support incidents to help Microsoft fix software bugs?

In my view that type of practice might serve as a deterrent for bug filing and follow-ups. I am sure this is not the desired effect.

I have filed many bugs on connect without any demands of opening a support case.

The response is usually rapid if you participate in beta testing of service packs.

Probably you pay for a quick delivery of hot-fixes.

HTH

Thomas Ivarsson

|||

No, you pay only if you support case wasn't MS error.

If you file an issue that is a bug, you pay nothing. For the hotfix you pay nothing. But the way from bug support case to the hotfix is some time long and difficult.

Do getDate() inside SQL Server OR do System.DateTime.Now inside application ?

For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value

OR

to do System.DateTime.Now in the application and then insert it in the table?

I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.

Thanks.

If you generate the data in code and then pass it to sql server over the network via a query, then you are simply generating more traffic for your network (albeit just a few extra bytes). If you use the built-in GETDATE() function you are saving yourself from having to pass that data over the network. I usually use GETDATE() myself.

|||

This depends on what you're doing. If you need the current time, it would be best to get the current time from whereever you need the time. (I know a guy who only needed the current time in his app, but created a call to the database's GetDate function, which was on another machine. That's not very accurate for the current application time.)

This may have been a big issue if your DB and app are on two or more machines, and the Server OS was older and it didn't have the ability to sync the time. I would say that the performance of the two functions would be really really close.

If you're worried about network bandwidth (if connecting with a sql server instance on another server), then having the database do the insert would be less of a load. If the column always displays the current time, then you could set the default for the column to GetDate(), eliminating the need to manually call the function or insert a value.

The biggest thing to thing about would be how important is this to you? There is the issue of what is deemed the "current time" -- Is the current time when the data was entered by the user at the application level, or is it current when the data is inserted into the table (use app's Now for the former, Sql's GetDate for the latter). For instance, there are cases where there may be a delay between a record being built and actually inserted into the DB. Every decision should be made based on frame of reference.

Again, my general suggestion is to use the function from the local source, unless you use the default value suggestion for your table's column.

|||

If the data is going to be stored in the database, I almost always use GetUTCDate() inside the database.

For web applications, this solves a number of problems. If you need to scale out to a web farm, you don't have to worry about keeping all the web farm machines (and possibly the SQL Server) clocks in sync with each other (Single time source). You don't have to worry about what timezone each of your webservers/users are in (Since the time is in UTC). That means I can take the easy way out and display the result in UTC or a fixed/hardcoded timezone to the user to start, and add user timezone support (user set, or autodetected) as the application matures, and I don't have to convert the data in the database when I do.

For desktop applications accessing the database, obviously keeping all the clocks synced is much more difficult, so not relying on the application layer to provide the current time greatly simplifies things.

In the past quite often a project starts as either a web application and then as it matures, we build a webservice for it or have desktop applications that need to access/create/import/export the data as well. Or the reverse, it's a desktop application and then use a web application for other parts/seconday interface into the data or migrate to a web application completely.

|||

Motley:

If the data is going to be stored in the database, I almost always use GetUTCDate() inside the database.

For web applications, this solves a number of problems. If you need to scale out to a web farm, you don't have to worry about keeping all the web farm machines (and possibly the SQL Server) clocks in sync with each other (Single time source). You don't have to worry about what timezone each of your webservers/users are in (Since the time is in UTC). That means I can take the easy way out and display the result in UTC or a fixed/hardcoded timezone to the user to start, and add user timezone support (user set, or autodetected) as the application matures, and I don't have to convert the data in the database when I do.

For desktop applications accessing the database, obviously keeping all the clocks synced is much more difficult, so not relying on the application layer to provide the current time greatly simplifies things.

In the past quite often a project starts as either a web application and then as it matures, we build a webservice for it or have desktop applications that need to access/create/import/export the data as well. Or the reverse, it's a desktop application and then use a web application for other parts/seconday interface into the data or migrate to a web application completely.

Thanks Motley. I've never had to figure out hardware allocations for a website before and I'm new to all of this. I understand where you may want 2 or 3 web servers simply due to heavy traffic and just keep 1 database server. But would there ever be a situation where 1 web server is enough and you may need more than 1 database server? Or maybe one might need more than 1 of each kind? Are there rules of thumb to use when deciding how to scale up?

|||

Yes, using multiple web servers that access a single database server is common.

Scaling out to multiple database servers is a LOT more complicated, so it's usually avoided if possible. That usually means build a bigger database server, and/or improve the web application code as much as possible first. This is a lot less common.

Scaling out a database server and only requiring a single web server is probably the rarest of them all. Now there may be reasons for a single web server and multiple database servers, like instancing, or web front-ending multiple database driven applications, but scaling out isn't usually one of them.

|||

Motley:

Yes, using multiple web servers that access a single database server is common.

Scaling out to multiple database servers is a LOT more complicated, so it's usually avoided if possible. That usually means build a bigger database server, and/or improve the web application code as much as possible first. This is a lot less common.

Scaling out a database server and only requiring a single web server is probably the rarest of them all. Now there may be reasons for a single web server and multiple database servers, like instancing, or web front-ending multiple database driven applications, but scaling out isn't usually one of them.

Thanks for the clarification, Motley. I have a question regarding backing up of data. Arguably, the contents of the database are the meat and potatoes of the website. Lose the database information and you'll lose everything, correct? So what do most people running 1 database server do to protect their lifeblood? Is the RAID 1 setup sufficient? If one of the drives go down, I'm ok right? I've heard of the databases having their own protection like failover (not even sure what this is), etc, but I'd imagine a RAID setup would make those superfluous features unnecessary.

|||

Motley:

If the data is going to be stored in the database, I almost always use GetUTCDate() inside the database.

For web applications, this solves a number of problems. If you need to scale out to a web farm, you don't have to worry about keeping all the web farm machines (and possibly the SQL Server) clocks in sync with each other (Single time source). You don't have to worry about what timezone each of your webservers/users are in (Since the time is in UTC). That means I can take the easy way out and display the result in UTC or a fixed/hardcoded timezone to the user to start, and add user timezone support (user set, or autodetected) as the application matures, and I don't have to convert the data in the database when I do.

For desktop applications accessing the database, obviously keeping all the clocks synced is much more difficult, so not relying on the application layer to provide the current time greatly simplifies things.

In the past quite often a project starts as either a web application and then as it matures, we build a webservice for it or have desktop applications that need to access/create/import/export the data as well. Or the reverse, it's a desktop application and then use a web application for other parts/seconday interface into the data or migrate to a web application completely.

I think I'm going to go with this solution. I can add another column to my user table called something like "timezone" and have them input their GMT offsets when they register their account. Then, on authenticated pages, I would add/subtract the offsets everytime I obtain datetime values. Does this sound like an ok route?

I just tested this on my localmachine. I'm in NY so my offset is GMT -5, however, when I looked in the database, the time stored was 4 hours ahead, not 5. What did I do wrong here?

Thanks.

|||

S2kDriver:

I think I'm going to go with this solution. I can add another column to my user table called something like "timezone" and have them input their GMT offsets when they register their account. Then, on authenticated pages, I would add/subtract the offsets everytime I obtain datetime values. Does this sound like an ok route?

I just tested this on my localmachine. I'm in NY so my offset is GMT -5, however, when I looked in the database, the time stored was 4 hours ahead, not 5. What did I do wrong here?

Thanks.

Don't let your app decide whether to add or subtract. Simply add the offset to the GMT. You may be having a mathematical error, such as GMT - -5, which would yield GMT + 5. If you simply add the offset, you should be fine, i.e.: GMT + -5 is the same as GMT - 5.

You may also be using GetDate() instead of GetUtcDate() in SQL Server; the former would return your local time instead of GMT/UTC.

|||

I did not do the math yet. All I did was run getUTCDate() on the local machine I'm using to test. I'm in NY (GMT -5), so the inputted data in the database should have been 5 hours ahead, but it was only 4. Sorry for the add/subtract confusion, yea I was just speaking affirmatively.

I'm sure I'm not using getDate(), if I were, the inputted data in the database would have been identical to the time I was seeing on the computer.

I've thought about the DST (daylight savings) factor, but I don't see why that would have any effect since the database just looks at the computer's settings (which time zone it is set to) and just compensates to arrive at GMT.

Did anyone else have this similar problem?

Thanks.

Do data mining results store at some where?

Do data mining results store at some files or in the database? I need the mining results at later time for reporting purpose.

In the SQL Server Data Mining architecture, mining models are first-class database objects (at the same level as cubes) in the Analysis Services database. When you process (train) a mining model, the patterns discovered are stored as part of the mining model object in the database. These can be accessed at any time by querying the mining model using the DMX query language. DMX allows you to explore the patterns in a trained model as well as apply them to new data to return predictions.

See the DMX documentaion in SQL Server 2005 Books Online for more information.

If you're specifically talking about embedding the output of the viewers in BI Dev Studio /SQL Mgmt Studio into reporting services, see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=369490&SiteID=1

DMX Query, Group by

I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.

Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?

This is what the DMX query looks like...

SELECT
t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName],
Sum(t.[Profit]) as Profit
From
[Revenue Estimate DT]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName],
[ChildrenStatusName],
[EducationName],
[Profit],
[IncomeName],
[HomeOwnerName],
[SexName],
[Country],
[ProductTypeCode],
[ProductName],
[MailCount],
[OrderAmount],
[SalesAmount],
[MailCost]
FROM
(SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount)
AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey
FROM [DataMining.RevenueEstimate.Predict]
GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID
HAVING (ClientID = 1)) as [Prediction]
') AS t
ON
[Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND
[Revenue Estimate DT].[Education Name] = t.[EducationName] AND
[Revenue Estimate DT].[Income Name] = t.[IncomeName] AND
[Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND
[Revenue Estimate DT].[Sex Name] = t.[SexName] AND
[Revenue Estimate DT].[Country] = t.[Country] AND
[Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND
[Revenue Estimate DT].[Product Name] = t.[ProductName] AND
[Revenue Estimate DT].[Mail Count] = t.[MailCount] AND
[Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND
[Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND
[Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND
[Revenue Estimate DT].[Profit] = t.[Profit] AND
[Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName]
GROUP BY t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName]

Hello

GROUP BY is not supported in DMX, and neither are temporary tables. A solution would be to execute the query, store the results inside SQL Server, then execute the group by inside the relational engine.

You can find some details on executing predictions from the relational engine in this article: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx

|||Or you could do as Bogdan suggests without storing the results and performing SQL operations on an OPENROWSET DMX query result

DMX query and ASP.Net

I am trying to get along with SQL server 2005, made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio. I cant get predection result in to aspx. I found this article but still...nothing

http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp

After spending some weeks of testing and reading I came up with this:

Itsa part of the code of cross web application, I managed to make a modelof timeseries in SQL server and I have hopefully (since I dont get anyerrors) run my dmx query through aspx. BUT I can get the result todisplay in aspx. My guess is because the original code was made forstring variables and I am tring to get numeric variables in it. Isuppose that all I need to do is to get the result from the DMX queryin the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If


'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks

Try changing

"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]

to

"SELECT PredictTimeSeries([Apot Sales],5)AS 'APOT' FROM Sales"

Giving the column a name should allow it to be displayed.

DMX query and ASP

I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.

SELECT PredictTimeSeries([Apot Sales],5)

FROM [Sales Bycom]

I tried running this as a query in ASP but obviously this can't be done

Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?

Thanx

You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:

Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks

|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results

in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"

here is the whole code from predict.vb.asp

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient

Namespace MovieCrossSellApplication

Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page

Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit

'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "

' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"

'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i

'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While

'Disconnect from Analysis Server
asSession.DisConnect()

End Sub 'GetRecommendations

Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click

' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i

' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()

' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)

' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click

End Class 'ShoppingBasket_Recommendations

'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"

Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New

Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog

asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()

Return True
End Function 'Connect

Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")

Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()

Catch e As Exception

Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult

Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect

Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log

End Class 'AnalysisServerSession

End Namespace 'MovieCrossSellApplication

Any help appreciated
|||OK I think I am getting somewhere......

Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....

If anyone could help or suggest a web site?

Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()

Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while

DMX query and ASP

I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.

SELECT PredictTimeSeries([Apot Sales],5)

FROM [Sales Bycom]

I tried running this as a query in ASP but obviously this can't be done

Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?

Thanx

You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:

Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results

in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"

here is the whole code from predict.vb.asp

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient

Namespace MovieCrossSellApplication

Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page

Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit

'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "

' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"

'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i

'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While

'Disconnect from Analysis Server
asSession.DisConnect()

End Sub 'GetRecommendations

Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click

' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i

' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()

' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)

' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click

End Class 'ShoppingBasket_Recommendations

'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"

Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New

Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog

asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()

Return True
End Function 'Connect

Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")

Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()

Catch e As Exception

Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult

Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect

Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log

End Class 'AnalysisServerSession

End Namespace 'MovieCrossSellApplication

Any help appreciated|||OK I think I am getting somewhere......

Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....

If anyone could help or suggest a web site?

Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()

Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while

DMX Query

Hi,

I am having a DMX Query as follows.

SELECT [Englishproductname],( SELECT $TIME, [Profit], PredictVariance([Profit]) FROM PredictTimeSeries([Profit],5)) FROM [ProductSales_Forecast]

It displays as
English productname and the Expression as columns.
The expression consists of time, profit as nested one.

I need to have no nested queries.
I want englishproductname to repeat for all the columns inside the expressions.
I don't want to display as expressions.
Repeatation is not a problem for me.

Plz help in this regard.

Tx in advance

Have you tried :

SELECT FLATTENED [Englishproductname],( SELECT $TIME, [Profit], PredictVariance([Profit]) FROM PredictTimeSeries([Profit],5)) FROM [ProductSales_Forecast]

If I understand correctly your requirements, this should do it

thanks

DMX Queries with MS Time Series

Hi!

I have a table Month_Sales(Month, product_1, .., product_n). The value of column product_i is the sale in this month.

so when i build MS Time Series for this domain, i want to query to find top m product is seld most in next month?

How do i buid that query?

DMX doesn't provide a way to order by the forecast value. However, you should be able to easily do this in client side code. Assuming your model looks like this:

CREATE MINING MODEL SalesForecast(
Product TEXT KEY,
Month LONG KEY TIME,
Sales LONG CONTINUOUS)
USING Microsoft_Time_Series

The following query will return the prediction for the next month for all products:

SELECT Product, Sales from SalesForecast

You can sort the query result by Sales in client code and pick the top N..

dm query taking long time

I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.

use MyDatabase

GO

select db_name(database_id) as 'Database', o.name as 'Table',

s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,

page_count, avg_page_space_used_in_percent, record_count,

ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,

schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s

join sys.objects o on s.object_id = o.object_id

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where db_name(database_id) = 'MyDatabase'

order by avg_fragmentation_in_percent desc

--order by avg_fragment_size_in_pages desc

--order by page_count desc

--order by record_count desc

--order by avg_record_size_in_bytes desc

Alright Chap check this out.

Your dev server has 18 user databases and the

sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED')

checks all the databases.

replace the first NULL with the DBID of the database mydatabase and try running it again.

Jag

|||Thanks. So, I can consider this to be normal behavior?

I reduced the number of user databases from 18 to 9. Now it runs in about 1 second. (Same query, I haven't yet made the change you suggested.) I think its safe to say that this query does not scale!
|||In 'DETAILED mode the dmv will read all pages that are used in a database. In the query that you specified, you indicated that you wanted to walk though all the pages in all the databases that were available on the system. If you have one big database with a lot of data, this command will take a long time because of all the I/O.

If you want faster (but less detailed) results, you can use 'LIMITED' mode.

Thanks,

dll locked by VS

Hi,
I'm new to VS-Development an have to write a little assembly for Reporting
Services. Every time a change something in the assembly, I call the
following .bat-file:
copy "D:\rw\Visual Studio
2005\wbiReports\wbiReports\bin\Debug\wbiReports.dll" "C:\Programme\Microsoft
SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\"
copy "D:\rw\Visual Studio
2005\wbiReports\wbiReports\bin\Debug\wbiReports.dll" "\\wbi-lyta\C$\Program
Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\"
copy "D:\rw\Visual Studio
2005\wbiReports\wbiReports\bin\Debug\wbiReports.dll" "C:\Programme\Microsoft
Visual Studio 8\Common7\IDE\PrivateAssemblies\"
But the last copy fails, if there is still one VS open. I normally have 3
projects open, 2 reports and the assembly-project. Now having to close not
only every project but even close all VSs is very annoying. Am I doing
anything wrong?
Regards,
RalphI have run into the same problem. The best workaround I found is to
use NUnit to do all of my testing for the assembly. When I'm done I
install it into the appropriate directories and modify and test my
reports. This allows me to avoid the problem for the most part.

division in a view

In trying to simplify an overly complex query [at one time, 1000+ views and
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:

> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff

distributor_admin Issue....But different than normal

I am having the common "distributor_admin does not exist" error when
trying to set up a machine as its own distributor. (First time after
setting up about 200 distributors). I get this whether I'm doing it
remotely, or on the machine itself.
The wrinkle is that the login "distributor_admin" does not exist at all
on the potential publisher (who, again, is going to be its own
distributor).
Do I need to reinstall MS-SQL to get that login created? Or can I do
it by hand somehow?
Or am I barking up the wrong tree?
Thank you!
-Joseph
What happens when you do a select @.@.servername?
Does it match what you get when you issue a xp_cmdshell 'hostname'?
if not you need to do the following
sp_dropserver @.server='oldServername'
sp_addserver @.server='NewServerName', @.local='LOCAL'
Then restart SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joseph" <josephsheppard@.gmail.com> wrote in message
news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...
>I am having the common "distributor_admin does not exist" error when
> trying to set up a machine as its own distributor. (First time after
> setting up about 200 distributors). I get this whether I'm doing it
> remotely, or on the machine itself.
> The wrinkle is that the login "distributor_admin" does not exist at all
> on the potential publisher (who, again, is going to be its own
> distributor).
> Do I need to reinstall MS-SQL to get that login created? Or can I do
> it by hand somehow?
> Or am I barking up the wrong tree?
> Thank you!
> -Joseph
>
|||Thanks, Hilary...There certainly was a difference...
I followed your instructions, and I'm going to take another stab at
setting distribution up.
You are the man! I'm going to have my boss order your book...
-Joseph
Hilary Cotter wrote:[vbcol=seagreen]
> What happens when you do a select @.@.servername?
> Does it match what you get when you issue a xp_cmdshell 'hostname'?
> if not you need to do the following
> sp_dropserver @.server='oldServername'
> sp_addserver @.server='NewServerName', @.local='LOCAL'
> Then restart SQL Server.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Joseph" <josephsheppard@.gmail.com> wrote in message
> news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...

distribution database size and other file questions

I think my distribution database blew up in size a long time ago for a
specific problem, and I don't know if its large size (34 GB) will cause
other problems. Is there a way I can flush out old irrelevent data and then
shrink it to a smaller size? The published DB is about 110 GB and has pull
subscriptions to 2 other servers.
Also, what types of RAID disks should the distribution database be placed
on? What about the temp db, log files, and main published database files?
The published database has a separate .ndf for non clustered indexes. Are
these better to be on the same disk or different disks?
Is there anywhere someone can point me to in order to find out more
information about this stuff? I have searched high and low, and can't find a
good place to research this info and apply the information to our particular
setting.
Thanks in advance
--Kristy
As the distribution database involves high write activity it should be raid
10. temp db and logs should be on raid 10 as well. If the database has over
20% of its io being write, it should be raid 10, otherwise make it raid 5.
Ideally the ndf will be on a separate physical disk on a separate array
(raid 5 as its high read activity normally).
Can you shrink the distribution db as it is? Also you might want to issue
the following select * from distribution.dbo.msreplication_status to tell
you how many commands are in the queue. If its small you should be able to
shrink the db, if it is large you have to work on getting these commands to
the subscriber database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
>I think my distribution database blew up in size a long time ago for a
> specific problem, and I don't know if its large size (34 GB) will cause
> other problems. Is there a way I can flush out old irrelevent data and
> then
> shrink it to a smaller size? The published DB is about 110 GB and has pull
> subscriptions to 2 other servers.
> Also, what types of RAID disks should the distribution database be placed
> on? What about the temp db, log files, and main published database files?
> The published database has a separate .ndf for non clustered indexes. Are
> these better to be on the same disk or different disks?
> Is there anywhere someone can point me to in order to find out more
> information about this stuff? I have searched high and low, and can't find
> a
> good place to research this info and apply the information to our
> particular
> setting.
> Thanks in advance
> --Kristy
>
|||Thanks for the info. Is there a place you can point me to that will teach me
how to determine this on my own? I have your Transactional and Snapshot
book; is it in there?
We only have RAID 10 and RAID 1 set up on our server. But I am moving the
files around because I see a lot of things that indicate file placement
causing performance problems.
I check on distribution.dbo.msreplication_status. I know I tried shrinking
it many months before, but nothing worked.
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> As the distribution database involves high write activity it should be
raid
> 10. temp db and logs should be on raid 10 as well. If the database has
over
> 20% of its io being write, it should be raid 10, otherwise make it raid 5.
> Ideally the ndf will be on a separate physical disk on a separate array
> (raid 5 as its high read activity normally).
> Can you shrink the distribution db as it is? Also you might want to issue
> the following select * from distribution.dbo.msreplication_status to tell
> you how many commands are in the queue. If its small you should be able to
> shrink the db, if it is large you have to work on getting these commands
to[vbcol=seagreen]
> the subscriber database.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
pull[vbcol=seagreen]
placed[vbcol=seagreen]
files?[vbcol=seagreen]
Are[vbcol=seagreen]
find
>
|||My mistake it is msdistribution_status.
There are some white papers on the Microsoft web site - try this one
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%238I%2302OBHHA.2276@.TK2MSFTNGP03.phx.gbl...
> Thanks for the info. Is there a place you can point me to that will teach
> me
> how to determine this on my own? I have your Transactional and Snapshot
> book; is it in there?
> We only have RAID 10 and RAID 1 set up on our server. But I am moving the
> files around because I see a lot of things that indicate file placement
> causing performance problems.
> I check on distribution.dbo.msreplication_status. I know I tried shrinking
> it many months before, but nothing worked.
> --Kristy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> raid
> over
> to
> pull
> placed
> files?
> Are
> find
>

Distributing Processing of SQL Server

Hi,
We have a SQL Server 2005 Enterprise database running on a HP ML 370
Server with 4 processors, which has a Windows .Net 2003 as OS. But at
a time process of Sql Server running on a single processor of the
server.
1. Has Sql Server 2005 this capability that could run on Multi
Processors? Could we obtain to this aim by configuring Sql Server? or
can we achieve it by configuring Operating System?
2. Our database grows so fast,that We suggest that we encounter with
response time delay problem after a while. Which Administration
technique (like Indexing, clustering, moving old data to a secondary
DB, ... ) is better for maintaining our database.
Thanks in advanced.
Regards
SajjadSQL Server uses multiple processors by default, though it gets into details
I don't know.
Check 2005 BOL for the following page:
(max degree of parallelism)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/4445b385-5992-4d2d-bfd7-9e8ad869c5c9.htm
Also:
RECONFIGURE;
and
EXEC sp_configure;
"Sajjad" <spirahesh@.gmail.com> wrote in message
news:1192975398.771008.300920@.e34g2000pro.googlegroups.com...
> Hi,
> We have a SQL Server 2005 Enterprise database running on a HP ML 370
> Server with 4 processors, which has a Windows .Net 2003 as OS. But at
> a time process of Sql Server running on a single processor of the
> server.
> 1. Has Sql Server 2005 this capability that could run on Multi
> Processors? Could we obtain to this aim by configuring Sql Server? or
> can we achieve it by configuring Operating System?
> 2. Our database grows so fast,that We suggest that we encounter with
> response time delay problem after a while. Which Administration
> technique (like Indexing, clustering, moving old data to a secondary
> DB, ... ) is better for maintaining our database.
> Thanks in advanced.
> Regards
> Sajjad
>|||> 1. Has Sql Server 2005 this capability that could run on Multi
> Processors? Could we obtain to this aim by configuring Sql Server? or
> can we achieve it by configuring Operating System?
SQL Server 2005 Enterprise Edition will use all available available
processors by default. However, a single query will use multiple processors
only with a parallel query plan. Only some types of queries will benefit
form parallelism.
> 2. Our database grows so fast,that We suggest that we encounter with
> response time delay problem after a while. Which Administration
> technique (like Indexing, clustering, moving old data to a secondary
> DB, ... ) is better for maintaining our database.
Most performance gains come from database and application design, query
tuning and indexing. Clustering is mostly for availability. Moving old
data to another database isn't usually needed to improve performance because
appropriate indexes can provide good response time regardless of table size.
Large tables (many millions or billions or rows) tables can be difficult to
manage. Partitioning can improve manageability without needing to move old
data elsewhere.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sajjad" <spirahesh@.gmail.com> wrote in message
news:1192975398.771008.300920@.e34g2000pro.googlegroups.com...
> Hi,
> We have a SQL Server 2005 Enterprise database running on a HP ML 370
> Server with 4 processors, which has a Windows .Net 2003 as OS. But at
> a time process of Sql Server running on a single processor of the
> server.
> 1. Has Sql Server 2005 this capability that could run on Multi
> Processors? Could we obtain to this aim by configuring Sql Server? or
> can we achieve it by configuring Operating System?
> 2. Our database grows so fast,that We suggest that we encounter with
> response time delay problem after a while. Which Administration
> technique (like Indexing, clustering, moving old data to a secondary
> DB, ... ) is better for maintaining our database.
> Thanks in advanced.
> Regards
> Sajjad
>