Run A Procedure, Or Query Every 5 Hours???

its possible make a procedure or something inside the sql server to run every 5 hour?? to make a update of a table

View Replies


ADVERTISEMENT

Breaking Down Total Hours Worked Into Day And Evening Hours

I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'

View Replies View Related

How Do I Call A Stored Procedure That Takes 5+ Hours To Run From An Asp.net Page?

Hello -

I need to call a SQL Server stored procedure, which takes over five hours to run, from an asp.NET web page. This procedure then calls a DTS package which is what takes 5+ hours to run. I need the user to be able to click on the 'run' button and have the page kick off the stored procedure (or the DTS Package if that will work instead) and display a message saying the load has begun and to check a 'status' link.

Right now I get a page timeout because it's waiting for results.

Any help would be greatly appreciated!
Thanks,
cat72

View Replies View Related

Converting Decimal Hours To Hours And Minutes

I have a float variable that holds a decimal number of hours.
 
So 1.5 equals 1 hour 30 minutes.
 
I need to change this to the format  1:30
 
Any idea how to do this?

View Replies View Related

Optimizin Query Which Is Running For Hours

Hi Can somebody suggest optimization on this query.
ITs running for hours,I have checked on mutiple Servers but dont know where the problem is.It starts when i add the not exists piece if the query.
Pls help

SELECT distinct
isnull(P.id,'') PersonID
, s.status
, isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') FirstName
, isnull(replace(Ltrim(Rtrim(P.MiddleName)),',',''), '') MiddleName
, isnull(replace(Ltrim(Rtrim(P.lastname)),',',''),'' ) LastName
, isnull(replace(Ltrim(Rtrim(P.Suffix)),',',''),'') Suffix
, isnull(replace(Ltrim(Rtrim(p.company)),',',''),'') Company
, isnull(replace(Ltrim(Rtrim(PA.AddressLine1)),',',' '),'') AddressLine1
, isnull(replace(Ltrim(Rtrim(PA.AddressLine2)),',',' '),'') AddressLine2
, isnull(replace(Ltrim(Rtrim(PA.City)),',',''),'') City
, State = isnull(replace(Ltrim(Rtrim(PA.State)),',',''),'')
, ZipCode = ISNULL(CASEWHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 3 THEN ('00' + (PA.Zipcode))
WHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 4 THEN ('0' + (PA.Zipcode))
WHEN SUBSTRING(LTRIM(RTRIM(PA.Zipcode)),6,1) = '-' THEN ISNULL(PA.Zipcode,'')
WHEN SUBSTRING(PA.Zipcode,6,1) = ' ' THEN ISNULL(PA.Zipcode,'')
ELSE substring(PA.Zipcode,1,5)+'-'+ substring(PA.Zipcode,6,4)
END ,'')
, b.code
, CommitteeTermID = ''
, RevenueCategoryid = ''
FROM vwpersons p with (nolock)
join vwsubscriptions s on p.id = s.recipientid
join vwcompanies c WITH (NOLOCK) on c.id = p.companyid
join vwbusinesscodes b WITH (NOLOCK) on b.id = c.primarybusinesscode
join vwRevenueCategorys rc WITH (NOLOCK)on rc.id = c.RevenueCategoryid
join vwPersonPrefAddr pa WITH (NOLOCK) on p.id = pa.id
WHERE NOT EXISTS (SELECT s1.recipientid FROM vwsubscriptions S1 WHERE S1.SHIPTOID =S.SHIPTOID
AND S.PRODUCTID = S1.PRODUCTID AND S1.STATUSID IN (1,2,3,11,12))
and p.id in (select recordid from vwTopicCodeLinks tcl
Where tcl.entityid = 457
And tcl.topiccodeid = 332
And tcl.value = 'Yes')
and ((p.PreferredAddress = 'Business Address' and p.BadBusinessAddress = 0)
or (p.PreferredAddress = 'PO Box Address' and p.BadPOBoxAddress = 0)
or(p.PreferredAddress = 'Home Address' and p.BadHomeAddress = 0))
and b.code in ( 'M2','V2','V5','W1','W2','W3','W4'
,'W5','W6','W7','W8','W9','WZ','X2','X3','X4')
and p.status <> 5
and S.statusID IN (4,5,6,7,8,9,10,13)
and S.productid in (23)
group by b.code ,p.id , p.firstname,p.lastname,p.MiddleName,p.suffix,
p.company,pa.addressline1,pa.addressline2,pa.city, pa.state,pa.zipcode, s.status

View Replies View Related

Help With MSSQL Query That Takes Hours To Run

I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):

RETAIL(code, CurrentLocation) ~ 2.6 million records

LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records

STAFF(ID, NAME) ~ 40K records

Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.

The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.

To perform the query, I created two views:

HISTORY
=======
CREATE VIEW HISTORY
AS
SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION
FROM LOCAUDIT

LASTHISTORY
==========
CREATE VIEW LASTHISTORY
AS
SELECT CODE, Max(UpdateDateTime) AS LastUpdated
FROM HISTORY
GROUP BY CODE

UPDATE RETAIL
SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END)
FROM RETAIL AS t4
LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE)
LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime)
LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)


What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).

In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.

However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..

View Replies View Related

Long Query Takes Hours...

Recently my system encounter some problem when retrieving certain record from MSSQL.
For an example i have a database which contains 1.5 million of members. so i have a perl scripts that will execute to query based on certain range.

the schedule like below:
1 script - 1-250k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 250k-500k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 500k-750k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 750k-1M (Query finish in 1++ hours)
<interval 5 mins>
1 script - 1M-1.25M (Query finish in 1++ hours)
<interval 5 mins>
1 script - 1.25M-1.50M (Query finish in 1++ hours)
END

After the 4th query, the query seems to work very slow, and this problem only raise on windows 2003 with mssql 2005, current server that run smoothly is win2k with mssql2000.

anyone have any idea on this problem either cause by operating system and database or related to something else?

View Replies View Related

SSRS Dataset Query Takes 2 Hours To Run

hello

I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005.
The BO reports used data regions based on multiple datasets, which is not feasible in SSRS.
So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database !
is there a better way to do this ?

here is the query





Code Snippet

ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values]
    -- Add the parameters for the stored procedure here
@TimePeriodDesc varchar(255),
 @TotalMarket varchar(255),
 @datasetname varchar(255),
 @VisibleProducts varchar(500),
 @DoctorSubTerritory varchar(255)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  xpospecialty.specialtydesc,
        a.doctorsubterritory      ,
        a.doctorbrick             ,
        a.doctorgroup             ,
        xpomarket.productdesc     ,
        s.totalmarketrank         ,
        s.prod1ranktotal          ,
        s.prod2ranktotal          ,
        SUM
        (
                CASE
                        WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN xpomeasures.rxvalues
                        ELSE 0
                END
        ) AS totalrxvalues,
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     <> 'Non -')
                            AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
                            AND (xpotimeperiod.timeperioddesc  = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS rxrcount,
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'High')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [high],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Medium')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [medium],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Low')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [low],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Very Low')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [very low],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Very High')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [very high],
        (SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
        FROM    dbo.xpotimeperiod
        INNER JOIN dbo.xpomeasures
        ON      xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
        INNER JOIN dbo.xpomarket
        ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
        INNER JOIN dbo.xpogeography
        ON      xpomeasures.geographyid       = xpogeography.geographyid
        WHERE (xpomarket.productdesc          = @TotalMarket)
            AND (xpomeasures.datasetname      = @DatasetName)
            AND (xpotimeperiod.datasetname    = @DatasetName)
            AND (xpomarket.datasetname        = @DatasetName)
            AND (xpogeography.datasetname     = @DatasetName)
            AND (xpogeography.doctorgroup     = a.doctorgroup)
            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
        ) AS rxvaluesdoctorgroup
FROM    dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON      xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.XpoMarketSize
ON      XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN dbo.xpoproductsize
ON      xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN dbo.xpomarket
ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography a
ON      xpomeasures.geographyid = a.geographyid
INNER JOIN dbo.xpospecialty
ON      xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON      xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN    (
            SELECT        xpogeography.doctorgroup,
                    SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
                    ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
                    ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
                    ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
            FROM        dbo.xpotimeperiod
            INNER JOIN    dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
            INNER JOIN    dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
            INNER JOIN    dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
            WHERE        xpomeasures.datasetname = @DatasetName
                        and (xpomarket.datasetname = @DatasetName)
                        and (xpogeography.datasetname = @DatasetName)

                    AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
            GROUP BY    xpogeography.doctorgroup
        ) AS s ON s.doctorgroup = a.doctorgroup
WHERE (xpomeasures.datasetname        = @DatasetName)
    AND (xpomarketsize.datasetname    = @DatasetName)
    AND (xpoproductsize.datasetname   = @DatasetName)
    AND (xpotimeperiod.datasetname    = @DatasetName)
    AND (a.datasetname                = @DatasetName)
    AND (XpoMarket.datasetname        = @DatasetName)
    AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
    AND (a.doctorsubterritory         = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
        a.doctorsubterritory       ,
        a.doctorbrick              ,
        a.doctorgroup              ,
        fn_mvparamsorted.parame    ,
        fn_mvparamsorted.sortid    ,
        xpomarket.productdesc      ,
        s.totalmarketrank          ,
        s.prod1ranktotal           ,
        s.prod2ranktotal
ORDER BY s.totalmarketrank,
        a.doctorgroup     ,
        fn_mvparamsorted.sortid
END

View Replies View Related

After Around 10 Hours, My SQL Will Produce &&"query Template Limit&&", Any Idea?

I am using SQLCacheDependency, after around 10 hours, all three webservces which connect to the same SQL will remove cached item immediately, after did some trace, I found start from the fail point, SQL produces "query template limit" subscribe.

 

Any idea why it happens? why the templates are not released? how to increase the limit?

 

thanks!

View Replies View Related

Replication Problem After Running For 24 Hours With Action Msg &&"could Not Query Row Metadata At The 'Subscriber'&&"

 

We are currently using a Merged replication and Push subscriber to replicate the databse from DB1 to DB2 every hours. The replication process successed for first 20 hours but we found that the replication process cannot be completed after 20 hours with the following error codes:





Action Code     Last Action Msg              

4                           The process could not query row metadata at the 'Subscriber'

363                       The process could not deliver insert(s) at the 'Subscriber'.





Thanks million for your kindly help!!!

View Replies View Related

Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access

For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.

ddave

View Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
 

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following:  EXEC dbo.SalesByCategory @Seafood.  I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood".  I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above).  Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
 
Thanks in advance,
Scott Chang 
    

View Replies View Related

Stored Procedure Query Optimization - Query TimeOut Error

How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :

CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN


SET NOCOUNT ON

SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec

RETURN 1
END



GO

View Replies View Related

SQL M.P.s - 1 Day = 24 Hours??? Or 24 - 48 Hours???

We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...

Is my assumption correct or do I need to look at something else. Thanks!

Billy

View Replies View Related

Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second

Hello to all,
I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters  direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha 
My Procedure Codes are here:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )
AS
BEGIN
 
if ( @Level = 1)
begin
select @Path = convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @IDMember
and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0
end
if (@Level = 2)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0
end
if (@Level = 3)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0
end
if ( @Level = 4)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0
end
if (@Level = 5)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0
end
if (@Level = 6)
begin
select top 1 @Path = '' from wtcomValidRelationships
end
END
 
 
 

View Replies View Related

Sum Of Hours By Week

Hi,I have the following query:SELECT p.employee_code, p.employee_name,CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) ASTotalHoursFROM tat_time t, hbm_persnl pWHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'AND '2005-03-31' AND p.employee_code IN ('0340')GROUP BY p.employee_code, p.employee_name, t.tran_date, t.post_dateORDER BY p.employee_codeI would like to sum the tobill_hrs by week (Monday-Sunday) instead ofby day. For instance the user will put in a date range (a from dateand to date).Is this possible to do?Thanks!

View Replies View Related

Time Over 24 Hours

I am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]

View Replies View Related

8 Hours To Add A Bit Column

I ran the following:

ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0


On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID

Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?

View Replies View Related

28+ Hours To Shrink?????

My database is current 135GB. I have been running the following:

DBCC SHRINKFILE([database_data],2)
DBCC SHRINKDATABASE ( [Database],20,TRUNCATEONLY)

It's been running for 28 hours. Should it be taking this long? If I stop the shrink, will it need to rollback?

Thanks!

View Replies View Related

Storing Hours

I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?

View Replies View Related

SQL Showing Last 24 Hours

SELECT sysjobs.name, sysjobservers.last_run_outcome, CONVERT(varchar(8), sysjobservers.last_run_date, 112) AS LastRunDate, CONVERT(varchar(8),
sysjobservers.last_run_time, 8) AS LastRunTime, GETDATE() AS CurrentDateTime
FROM sysjobs LEFT OUTER JOIN
sysjobservers ON sysjobs.job_id = sysjobservers.job_id
WHERE (sysjobservers.last_run_outcome = 0) AND (CONVERT(varchar(8), sysjobservers.last_run_date, 112) >= DATEADD(day, - 1, GETDATE()))

I am wanting a similar solution, I need to convert an "int" to a time format, when I use this where ">= DATEADD(minute, - 720, GETDATE())" for the last_run_time I get this error:

Syntax error converting datetime from character string.

View Replies View Related

Select - 4 Hours

Hi,

I need to make a select getDate() subtracting 4 hours...

How can I make it ???

 

Thanks

View Replies View Related

Working With Hours

Would you help me ?
 
In the example below, how can I do to have count = 9 ?
 
It means that I have 9 periods of 30 minuts from 09:00 to 13:30. 
 
Thank you.  

declare initial_hour as varchar(10)
declare final_hour as varchar(10)
declare per30min as varchar(10)
 
declare count as int
 
set initial_hour = '09:00'
set final_hour = '13:30'
set per30min = '0:30'
 
set count = (final_hour - initial_hour) / per30min   ------>>> count = 9

 
 

View Replies View Related

Subtracting Hours

I would like to know the best method or the simplest method to do the following.  Take a datetime value and subtract a given number of hours.  I am taking a UTC time and subtracting a given number or hours in a query.  I seem to be getting mixed results.
 
Thanks

View Replies View Related

GMT Deviation Hours

Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.
 
 
Thanks
 
 

View Replies View Related

Comparing Hours

Hi, i have this problem :

I have to compare 1 hour between two days , example:

I have this datetime value (BitacoraProcesoUsuario.FechaInicioProceso) in 108 format : 23:35:22

and I make this query:

Select TOP 100 * from BitacoraProcesoUsuario
INNER JOIN Turno   
ON convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) > = '24:00:00'
AND  convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) <= '06:29:00'

and returns nothing........ this because 23:35:22 is gtreater than 06:29:00' value, my question is how can I compare this hours in this format.

Regards

View Replies View Related

Select Query Vs Store Procedure Query

hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..

select * from authors

OR


create procedure authors as

select * from authors





lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input

Ali

View Replies View Related

Select Query Vs Store Procedure Query

hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..

select * from authors

OR


create procedure authors as

select * from authors





lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input

Ali

View Replies View Related

SQL/ASP.net Storing Hours Information?

MS SQLJust to confirm, I need to store the number of hours a user has spent doing something, am I correct using the smalldatetime field for the MS SQL database?I will eventually need to be able to calculate totals using SQL Reporting Services so it's important that the hours add up correctly (to 60 minutes, not 100).ASP.NETI'm trying to find the correct sort of formatting to use? Short time stores the time as 9:50AM, which isn't what i'm after.I need to store the hours as in 1 hour, or 01:00:00 (1 hour, 0 minutes, 0 seconds) or just 01:00 for 1 hour. I do need minutes but seconds are not required.I can't find the right formatting for this, would Long time be more suited?

View Replies View Related

What Data Type Is Best For Hours

What is the best data type to use when tracking hours, for example;
2.30 < this is stored in DB
(two hours and thirty minutes)
5.15 < this is stored in DB
(5 hours and fifteen minutes)

View Replies View Related

Removing Out Of Hours Time

Hello Chaps,Just a little sounding on removing out of hours from some datetimedate that I have.Basically we have a helpdesk that logs calls when they are entered andwhen they are resolved. Now, if this happens during the day we justsubtract one date from the other to give the time it took to resolvethe issue. If it is not solved during the day we have an additional 11hours to add due to people going home and not worrying about peoplesprinters which is good but it buggers up stats that I'm trying toproduce.I haven't got anywhere with this at the mo and am looking at a routinethat compares the resolved time and see's is there is more that 24hours difference and if so remove 11 hours but wondered if you brightsparks had any ideas(also once I write something I tend to find the answer straight afterto confirm me stoppidity).Any ideas let us know.Ginters

View Replies View Related

Getting The Number Of Hours In A Month

Hi,

I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.

Any ideas?

Thanks,

Skip.

View Replies View Related

Adding A BIT Column - 100+ Hours!

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved