Snapshot Agent Failed From Time To Time
Sep 15, 2000
Hello !
I just encountered a problem with snapshot replication or, if more detailed, with snapshot agent.
My environment consist of W2K Server (sp1) and SQL Server 7.0 (sp2)
The issue is when I create publication and then start snapshot agent for initial creation of schema and articles, it fail with the error:
"An exception occurred in the Snapshot subsystem. The step failed."
Event Log registers this error with the following message:
-------------------
"Step 2 of job --- () has caused an exception in the Snapshot subsystem and has been terminated"
-------------------
the step 2 in REPL-Snapshot job is "Run Agent"
Once more detail. The metter is that this occurs not consistently but from time to time. Some publications succeed in snapshot process but others don't. And else, if I want to manually run snapshot agent for the second time after successful completion for the first, I get the same error.
Any idea?????
Thanks in advance
Vyacheslav
View 1 Replies
ADVERTISEMENT
Oct 17, 2006
Hi!
Well..
There's operable web sync with parameterized filter.
But sometimes the strange errors appear.
Below the list of the errors I got from ComErrorCollection property of MergeSynchronizationAgent instance:
ERROR: -2147199433
SOURCE: Merge Replication Provider(Web Sync Server)
TEXT: The Merge Agent was unable to start the SQL Server Agent job to generate the partitioned snapshot for this subscription. Verify that the SQL Server Agent service is running at the Distributor.
ERROR: 22022
SOURCE: HOST3MAIN
TEXT: SQLServerAgent Error: Request to run job dyn_HOST3MAIN-Customers-Main-2__20061014_14 (from User distributor_admin) refused because the job is already running from a request by User distributor_admin
ERROR: 20633
SOURCE: HOST3MAIN
TEXT: Failed to start the dynamic snapshot job. Verify that the SQLServerAgent service is running on the distributor.
ERROR: 20628
SOURCE: HOST3MAIN
TEXT: Failed to generate dynamic snapshot.
I'm sure that SQL Server Agent is running...
What is the reason and how it can be solved?
Thanks
Paul
View 6 Replies
View Related
Aug 4, 2006
Hello, need some guidance.
I noticed that my snapshot agent never got moving at all and has been failing. It keeps on generating errors in the System Log with every retry...
The server {08B0B2D9-3FB3-11D3-A4DE-00C04F610189} did not register with DCOM within the required timeout.
and this
SubSystem Message - Job 'mws-cpmorder-CPmOrder_Mobile-2' (0x669FF0D471C2A54FA11F764B1E3B0DFD), step 2 - Server execution failed
in the application log.
Any help is appreciated.
Rgds
View 1 Replies
View Related
Jul 6, 2007
When running the snapshot agent for a new publication I get the message:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strDataFile)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutWorkItem.DoWork(MergeSnapshotProvider snapshotProvider, IBcpOutProvider bcpOutProvider)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Message: COLV_90_TO_80: Cannot convert parameter param1: Resulting colv would have too many entries.
Stack: (Source: MSSQLServer, Error number: 20005)
Get help: http://help/20005
Message: The statement has been terminated.
Stack: (Source: MSSQLServer, Error number: 3621)
The database has another publication, that was created prior to upgrade to sql server 2005 from sql server 2000 sp4. This publication is working ok, and there are no problems when running the snapshot agent for this publication.
The publications are using filtered articles, and all properties for the publications are the same.
Any help to solve this problem will be much apreciated.
View 3 Replies
View Related
Jul 8, 2005
We've got a problem with our replication. If I try to run the snapshot agent I get the message "Query for data failed".
The detail view of this message shows the following:
View 4 Replies
View Related
May 22, 2006
Hello, I've configured SQL Server 2000 for merge replication and am trying to start the snapshot agent for the publication and after about 20 minutes of it trying to start, it gives up with this error.
Server execution failed
There is no more information in the event viewer. I've tried it with a bunch of different subscriptions. SQL Server and SQL Agent are running under a local account which has access to the replcation data directory. SQL Agent is running.
I'm Running SQL 2000 SP3.
Thanks
Robert Brown
View 3 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Aug 26, 2007
How can I, with TSQL script, know when a snapshot run last time?
Thank You.
View 3 Replies
View Related
Nov 20, 2007
I have various reports which have in the header a textbox with a time/date expression.
e.g. =Format(Now, "HH:mm on dddd, d MMMM yyyy")
These reports have a daily snapshot taken so users can look up historic versions of reports. The problem is that if the PDF renderer is used the time displayed is the time now not the time the data was generated. (If I view the snapshot in the browser the correct historic time and date is shown though.)
This is at best confusing and at worst could lead to some bad business decisions being made.
Is there any way I can get it to display the actual time and date the snapshot was generated?
I have tried replacing "Now" with "Globals!ExecutionTime" with no effect.
I have also seen a suggestion to move the header textbox into the report body and then add a new textbox to the header pointing at the textbox in the body
e.g. with Expression =ReportItems("LabelToday").Value
But this does not work either and it is still displaying the time now when viewed in PDF format.
Is there any straight forward way of doing this?
If there is no straight forward way can I somehow access the HistoryID from within Report Code so I can look up the correct value myself in the ReportServer database?
Thanks In Advance,
Martin
View 1 Replies
View Related
Apr 5, 2006
We have transactional replication running with a seperate publisher/distributor/subscriber. I want to add a couple of articles to the publication, and then initialise them. I have added the articles and run sp_refreshsubscriptions
I now want to refresh the subscriptions. I have selected not to lock the tables on the snaphot tab of the publication properties, but whenever I run the snapshot agent it locks the application solid! Its odd, as soon as I run the snaphot agent, the phones start ringing within minutes. The application is Great Plains and I have set the snapshot agent to run nightly anyway.
Is there any way I can run the snapshot agent during working hours to refresh this one article? Once I have successfully done this, I have a number of articles want to add - but I can't lock the tables when refreshing the initial snaphot.
View 1 Replies
View Related
Sep 11, 2007
SQL Server 2005 SP2 Error: Script failed for Table 'dbo.TableName'
I keep getting this error when trying to generate a snapshot for transact replication.
Here is the publishing table schema:
CREATE TABLE [dbo].[viMediaPlaylist](
[MemberID] [bigint] NOT NULL,
[xmlPlaylist] [xml](CONTENT [dbo].[viMediaPlaylistCollectionSchema]) NOT NULL,
PRIMARY KEY CLUSTERED
(
[MemberID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
I tried the following:
#1)Create table schema at the subscriber manually, and change package to Keep object as is. Got the same error during snapshot.
#2Filtered replication to NOT include XML column. This worked, and no error was generated, replication was up and running.
So my question is, what is the problem with XML column being replicated?
Any ideas how i can make this work? At this point i'm not sure what the problem is with the xml column, but it seems like ti tries to run schema script, but i'm not asking it to create the schema i already did it myself. Thanks!
Here is a log dump from the distributor:
2007-09-11 16:40:14.56 SQL Command dump
2007-09-11 16:40:14.56 ================
2007-09-11 16:40:14.56 Server: SQL02
2007-09-11 16:40:14.56 Database: Video
2007-09-11 16:40:14.56 Command Text: sys.sp_releaseapplock
2007-09-11 16:40:14.56 Parameters:
2007-09-11 16:40:14.56 @Resource = SQL02-Video_viMediaPlaylis-71
2007-09-11 16:40:14.56 @LockOwner = Session
2007-09-11 16:40:14.56 @DbPrincipal = db_owner
2007-09-11 16:40:15.17 [0%] The replication agent had encountered an exception.
2007-09-11 16:40:15.17 Source: Unknown
2007-09-11 16:40:15.17 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2007-09-11 16:40:15.17 Exception Message: Script failed for Table 'dbo.viMediaPlaylist'.
2007-09-11 16:40:15.17 Message Code: Not Applicable
2007-09-11 16:40:15.17
2007-09-11 16:40:15.17 Call Stack:
2007-09-11 16:40:15.17 Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'dbo.viMediaPlaylist'. ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: Either the object or one of its properties is not supported on the target server version.
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.GetTypeDefinitionScript(ScriptingOptions so, SqlSmoObject oObj, String sTypeNameProperty, Boolean bSquareBraketsForNative)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.AppendScriptTypeDefinition(StringBuilder sb, ScriptingOptions so, SqlSmoObject oObj, SqlDataType sqlDataType)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 --- End of inner exception stack trace ---
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.AgentCore.Run()
View 6 Replies
View Related
Aug 22, 2007
In SQL 2000 you can schedule a job to run each time the agent starts. I don't see that option now in 2005.
Does anyone know what happened to that or how to achieve that. I could do a stored proc with the proc option I guess but why did they remove that functionality or maybe I am not seeing it.
Thanks
View 1 Replies
View Related
Sep 14, 2006
I have set up several jobs to run automatically throughout the day. Each job just does one thing: executes an SSIS package.
The packages themselves run perfect when I test them in Visual Studio 2005, but when the SQL Server Agent tries to run the job I am getting failed jobs.
Is there any tutorials or walkthroughs that can teach my about using the SQL Server Agent? If I wanted extensive help troubleshooting my SQL Server Agent problems which part of the message boards would I post that too?
Thanks for the info!
View 6 Replies
View Related
Sep 1, 2015
Below the Error Log Reported in SQL Agent (64 bit SQLServer 2008R2 Enterprise Edition Service pack 2 ,32Core CPU Numa Node and 64GB Ram
Â
[298] SQLServer Error: 10054, TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01]
[298] SQLServer Error: 10054, Communication link failure [SQLSTATE 08S01]
[165] ODBC Error: 0, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online. [SQLSTATE 08001]
[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
View 2 Replies
View Related
May 14, 2014
I am getting the following error :
conversion failed when converting date and or time from character string
I am using Sql Server 2008.(database designed for sql 2005 later moved to sql server 2008).
Pickup_time and actual_Pickup_time are varchar(5) in database.
What is wrong with this query?
Query:
SELECT COUNT(Trip_ID) AS OntimePickupCount
FROM MyTABLE
WHERE Start_Dt BETWEEN '01/01/2014' AND '04/30/2014'
AND (DateDiff(minute, CAST (Pickup_Time AS time), CAST (Actual_Pickup AS time )) BETWEEN 0 AND 15
OR DateDiff(minute, CAST (Actual_Pickup AS time), CAST (Pickup_Time AS time) ) BETWEEN 0 AND 15)
AND Actual_Pickup IS NOT NULL AND Actual_Dropoff IS NOT NULL
View 4 Replies
View Related
Apr 25, 2014
Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
View 7 Replies
View Related
Jun 11, 2015
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies
View Related
Oct 22, 2015
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?Â
View 2 Replies
View Related
Oct 3, 2015
I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
View 14 Replies
View Related
Dec 15, 2006
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View 1 Replies
View Related
Apr 21, 2015
SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
Nov 12, 2007
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
View 8 Replies
View Related
Sep 21, 2015
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies
View Related
Dec 26, 2014
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
View 2 Replies
View Related
Oct 7, 2015
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
View 0 Replies
View Related
Feb 19, 2008
Hi all,
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
View 3 Replies
View Related
Apr 30, 2015
we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.
View 2 Replies
View Related
May 26, 2005
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
View 2 Replies
View Related
Aug 17, 2007
Ok, so I have some horribly convuluted SQL that I would love to optomize. I'm not happy leaving it in it's current state, that's for sure!
I'm currently working on our test bed servers, so obviously my stats are out because of the "crap-ness" (yes, that's the technical term) of the hardware, but still, it should NEVER need to take this long!!
Basically, the issue arises in the nasty join to the career table (one employee can have multiple career lines). Just to make things complicated, employees can have any number of career records on any given date, these can even be input for future career events. The following SQL picks out the latest-current career date for each employee based on the career_date being <= GetDate() and the date of entry for this date being the greatest.
E.g.
career_date | datetime_created
2009-01-01 | 2006-05-05 13:55:21.000
2007-01-01 | 2006-05-05 13:54:18.000
2007-01-01 | 2006-05-05 13:52:55.000
From the above we want to return
2007-01-01 | 2006-05-05 13:54:18.000
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.sAMAccountNameAs 'sAMAccountName'
, a.userPrincipalNameAs 'userPrincipalName'
, 'TRUE'As 'Modify'
, RTRIM(e.unique_identifier)As 'employeeID'
, RTRIM(e.employee_number)As 'employeeNumber'
, RTRIM(e.known_as)
+ CASE WHEN RTRIM(e.surname) IS NOT NULL THEN
' ' + RTRIM(e.surname) ELSE NULL ENDAs 'displayName'
, RTRIM(e.known_as)As 'givenName'
, RTRIM(e.surname)As 'sn'
, RTRIM(c.job_title)As 'title'
, RTRIM(c.division)As 'company'
, RTRIM(c.department)As 'department'
, RTRIM(l.description)As 'physicalDeliveryOfficeName'
, RTRIM(REPLACE(am.dn,'\',''))As 'manager'
, t.full_mobile
+ CASE WHEN RTRIM(t.mobile_number) IS NOT NULL THEN
' (DD: ' + RTRIM(t.mobile_number) + ')'ELSE NULL END
As 'mobile'
, t.mobile_numberAs 'otherMobile'
, ad.address_ad_countryAs 'c'
, ad.address_ad_address1
+ CASE WHEN ad.address_ad_address2 IS NOT NULL THEN
', ' + ad.address_ad_address2 ELSE NULL END
+ CASE WHEN ad.address_ad_address3 IS NOT NULL THEN
', ' + ad.address_ad_address3 ELSE NULL END
+ CASE WHEN ad.address_ad_address4 IS NOT NULL THEN
', ' + ad.address_ad_address4 ELSE NULL END
+ CASE WHEN ad.address_ad_address5 IS NOT NULL THEN
', ' + ad.address_ad_address5 ELSE NULL ENDAs 'streetAddress'
, ad.address_ad_poboxAs 'postOfficeBox'
, ad.address_ad_cityAs 'l'
, ad.address_ad_CountyAs 'st'
, ad.address_ad_postcodeAs 'postalCode'
, RTRIM(ad.address_ad_telephone) +
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN
' (Ext: ' + RTRIM(a.othertelephone) + ')'
ELSE
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NULL THEN
'Ext: ' + RTRIM(a.othertelephone)
ELSE NULL
END
ENDAs 'telephoneNumber'
FROM employee e
LEFT
JOIN career c
ON c.parent_identifier = e.unique_identifier
AND c.career_date =(
SELECTmax(c2.career_date)
FROMpwa_master.career c2
WHEREc2.parent_identifier = c.parent_identifier
ANDc2.career_date <= GetDate()
)
AND c.datetime_created =(
SELECT max(c3.datetime_created)
FROMpwa_master.career c3
WHEREc3.parent_identifier = c.parent_identifier
ANDc3.career_date = c.career_date
)
LEFT
OUTER
JOIN AD_Import am
ON am.employeeNumber = c.manager_number
INNER
JOIN AD_Import a
ON a.employeeID = e.unique_identifier
LEFT
JOIN AD_Telephone t
ON t.unique_identifier = e.unique_identifier
LEFT
JOIN AD_Address ad
ON ad.address_pwa_location = e.location
LEFT
JOIN xlocat l
ON l.code = c.location
WHERE (a.employeeNumber IS NOT NULL
OR a.employeeID IS NOT NULL)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1706 row(s) affected)
Table 'AD_Import'. Scan count 4, logical reads 106, physical reads 0, read-ahead reads 0.
Table 'AD_Address'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'AD_Telephone'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 868, logical reads 956, physical reads 0, read-ahead reads 0.
Table 'xlocat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.
Table 'career'. Scan count 5088, logical reads 2564843, physical reads 0, read-ahead reads 0.
Table 'people'. Scan count 1697, logical reads 5253, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 826, logical reads 914, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15203 ms, elapsed time = 8114 ms.
Any advice on what I can do to optomize?
Oh judt to point out that "employee" is a view on the "Table 'people'."
EDIT: I know it's pointing out the obvious, but I'm pulling out the managers "DN" from AD_Import based on the manager_number and employeeNumber matching.
View 14 Replies
View Related
Dec 18, 2007
I need a formula to calculate the time (let's say in minutes) between two dates/times.
The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday).
I will use this in a couple of reports made in Reporting Services.
If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful.
Many thanks!
/Per Lissel
View 3 Replies
View Related
Apr 10, 2007
Hi all,
I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?
Thanks,
Ning
View 5 Replies
View Related
Dec 19, 2006
I wanted to know how we can handle Time Zones and Daylight Savings Time in SQL Server 2000 as well as 2005.
Any pointers would be helpful.
Pranav
View 1 Replies
View Related
Sep 14, 2015
At the moment I already added the DataSet I'm gonna be using. I have 2 date parameters Start Time and End Time.
What I would like to do  for the report is to only pull results greater than 48 hours to the report
How can I accomplish this?
View 4 Replies
View Related
