Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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 aMerged 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 inmy 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 thecode of ConnectionPoolingFormto 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 gota 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 inthe 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'
setper30min = '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
Hours Days And Months
This code displays records by the the month they where recorded and displays the months as names (January...)
How can I do the Same for Days (Mon, Tues) and Hours (9.00,10.00)

Select DATENAME(mm,dDate) as Month, count(iStatID) as Total_Po
from ListingStats
Group by DATENAME(mm,dDate)
Order by DATENAME(mm,dDate)

View Replies !   View Related
Delete Files More Than N Hours
Hi everybody,

Can anyone help me out in deleting files more than n- hours from a physical folder using xp_cmdshell ....

View Replies !   View Related
Computing Business Hours
I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

Essentially I have a number of different alternatives, and have found some potential solutions, including:

www.dbforums.com/arch/7/2003/9/914261

However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

Additionally, I would like to remove Holiday's from the calculation for service level as well.

I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

The DTD I have for the Holiday table is as follows:

CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')


Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

Thank you in advance

View Replies !   View Related
Calculating Business Hours
I am trying to calculate business hours that an order that is open. The rules are 8am-5pm. For instance the first row, the clock would stop at 17:00, and pick up again at 8am and add on to total business hours. If the order was created after business hours, and the endstamp was before business hours, this would be 0. If created after 5pm friday, and the endstamp was before 8am monday, this would be 0. In the second set of timestamps I have here the order was recieved at 14:39 but took until the following day at 14:49 to be ordered. I imagine I have to use datepart and datediff for this, but other than that I am not sure on how to do it. Any help would really be appreciated!



StartStampEndstamp
6/27/2005 14:356/27/2005 17:41:11
6/27/2005 14:396/28/2005 14:49
6/27/2005 18:486/27/2005 18:54
6/27/2005 11:416/27/2005 11:45

View Replies !   View Related
DBCC Shrinkdatabase --run 36 Hours....
Hi:

database AB with 195 gb on a SQL2000 sp3 server.
I have dropped 10 tables in the DB A, each about 2 gb.

DBCC Shrinkdatabase(AB, 10) has run 36 hours and still running. It looks it using many CPU and I/O but with 0 in memory.

I have done dbcc checkdb with 0 error.
I have done sp_statistics
both above return results had excluded dropped tables.

Related Jobs and DTS run this DB are as normal.
Before this, I have tried DBCC shinkfile(A_dataFile, 10) and I had cancelled it after run 24 hours.

The server free space is around 36 gb now.
Any idea? :rolleyes:
thanks
David

View Replies !   View Related
Can&#39;t Add New Records During Office Hours
I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.

Debbie

View Replies !   View Related
Dates And How To Set Their Hours In Ms Sql Server?
hello this is my store procedure and i want to set the hour of the first date to be 00:00:00 and the hour of the second date to be 23:59:59 but i don't know how to do this,so please HELP me

CREATE PROCEDURE dbo.Proba
@date1 smalldatetime ,
@date2 smalldatetime,
@s float output
AS
Select @s=sum(Pay) from RKO where Dates between @date1 AND @date2
GO

View Replies !   View Related
How Can I Work Out X Hours Y Mins?
In a stored procedure, I need to work out, how long has elapsed since a specified date. This is easy enough to work out in any unit of time with the DATEDIFF function.

However, what I really need though is for this to be outputted in the number of hours AND minutes.

i.e. '9 hr 55 mins'

This would be done by casting as varchar from the DATEDIFF function but I can't quite work out how to get hours AND minutes....? any suggestions?

View Replies !   View Related
Days Hours Minutes
I have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...

TimeValue({clm_doubt}-{clm_rcvd})

This is not working for me. Can someone give me a suggestion please.

Thanks!

View Replies !   View Related
Hours And Minutes Question
Hi guys,

how to get the Hours and Minutes ago?

I have a column name "DateCreated" which is a DateTime type. I need to get the Hours and Minutes ago

Thank you.

View Replies !   View Related
Calculate Hours And Minutes
Hi
Can anyone help me convert a number to give the result in hours and minutes? For example 195 as 3:15 or 210 as 3:30. We are trying to create a report showing hours and minutes worked without having to export to Excel.

I've had a look around the net and this seems to be quite a difficult function in SQL Server.

Any guidance much appreciated.

View Replies !   View Related
RESTORE Takes Hours..
I've restored a database by the standard menu (the backup was a .bak file).

It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.

What shell I do?

View Replies !   View Related
Restore Hangs For 5 Hours?
Had a problem in a database and wanted to restore from disk as of 11/5/2007 (full backup). This particular database is only really updated about once a month, and changes were made on the sixth, so I thought this would be safe.

So (be kind - not an SQL guru), I ran something similar to:
RESTORE DATABASE myDatabase
FROM DISK = '\serverNamesqlbackupsmyDatabasemyDatabase_backup_200711052320.bak'
WITH NORECOVERY, STOPAT = 'Nov 05, 2007 11:21 PM'

myDatabase_backup_200711052320.bak is 42,084kb and has been running since 9:30 this morning (it's 2:20ish now). I've tried to take offline, stop/start sql services, restart server. Nothing works. It just keeps reading myDatabase (restoring).

Anybody help me out? How do I get it to stop and then do a clean restore?

Thanks so much,
Janet

View Replies !   View Related
SQL Mgt Studio Becomes Very Slow After A Few Hours
Hi everyone,

I'm connecting to my LOCAL MSSQL 2005 database using Management Studio. I find that after a few hours of use, Management Studio's connection to the database becomes extremely slow. For example, if I right click on a table and choose Open Table, it will take at least 30 secs to open the table. Then if I make changes to a record and move the cursor to the next row, it will take another 30 secs to update the record.

I don't think it's anything to do with indexes, cos my table is very small (less than 20 records).

I'm not running any batch jobs on my local database. In fact, my SQL Agent is turned off.

I notice this does not happen when I connect to my REMOTE database, which is hosted on a commercial web host.

What could be wrong here? Has anyone experienced this before?

Thanks for the help.

View Replies !   View Related
Synchronization Running For 2 Hours Already
I went to View Synchronization status and see that the sychronization has been running for two hours already. Is that normal? I thought it would just find the one change I made and update that one change?

Thanks in advance!

View Replies !   View Related
Showing Records Between Certain Hours
Hello All,

I am trying to create a parameter that would show me the aggregated details of records happening within a certain time range on a given day. Some days the range of from hour - to hour changes. I can do it using CASE is the hours are the same each day but am having difficulty writing a statment the differs from day to day. Here is what i think it should look like...

((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 0 AND 2 OR datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN datepart(dw, TestDateTime) = 2 AND datepart(hour, TestDateTime) BETWEEN 6 AND 23 OR datepart(hour, TestDateTime) BETWEEN 23 AND 23 AND datepart(minute, TestDateTime) < 59 THEN 1 WHEN Datepart(dw, TestDateTime) BETWEEN 3 AND 7 AND datepart(hh, testDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 23 AND 23 OR datepart(mi, TestDateTime) < 59 THEN 1 ELSE 0 END) >= @ShowCore)

Basically the core hours I am trying to show on the @ShowCore parameter is Sunday from Midnight to 2am and then 8am to 10pm, Monday from 6am to midnight(11:59) and then on Tuesday throught Saturday from Midnight to 2am and 6 am to 11:59.

I was hoping some could rewrite my code in correct syntax as I am have a rough time figuring it out.

Thanks much.

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved