Weird DateTime Value
Dec 20, 2004
I have a datetime field with a value of '-28049-03-16 10:01:16.267' in SQl Server 2000. I haven't a clue how or why this value was entered as I did not design or write the database. However, I do have the task of writing a DTS package to export the data to Access. The problem is that the DTS fails when it tries to export the record with the above value into an Access table with a Date/Time field. I thought I could get round this by using a UDF to say if the date equals this value then make it null:
RETURNS DateTime
As
BEGIN
DECLARE @CheckDate varChar(300)
SET @CheckDate = CONVERT(varChar(300),@Date)
IF (@CheckDate = '-28049-03-16 10:01:16.267')
BEGIN
SET @Date = Null
END
RETURN (@Date)
END
However when I use the UDF it gives me the following error:
Server: Msg 542, Level 16, State 1, Procedure UDFn_CheckDate, Line 23
An invalid datetime value was encountered. Value exceeds the year 9999.
I have tried many variations of Convert, Cast etc. but still can't get it to work.
Anybody got any ideas???
View 8 Replies
ADVERTISEMENT
Jan 15, 2008
So what I'm trying to do is audit changes on a server. I'm creating a DDL trigger as below:
Code Block
CREATE trigger DDL_changeTracking_tr
on Database
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
BEGIN TRY
BEGIN
declare @login varchar(100)
set @login = eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)')
if (@login <> 'sqladmin' and @login <> 'sqlagentadmin')
BEGIN
insert into DBMonitoring..audit_tbl (databaseId, auditTime, loginName, objectName, objectType, eventType)
select
DB_ID() as databaseId
, getDate() as auditTime
, eventData().value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(100)') + '.' +
eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)') as objectName
, eventData().value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(100)') as objectType
, eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)') as LoginName
, eventData().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)') as eventType
END
END
END TRY
BEGIN CATCH
BEGIN
declare @html varchar(max)
select @html = '<html>' + getDate() + '</br>' + eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
+ '</br>' + ERROR_MESSAGE() + '</html>'
PRINT 'Warning: Unable to submit change to audit'
SELECT ERROR_MESSAGE()
exec util_EmailOut_DatabaseMail_prc @from = '<address>',
@to = '<address>',
@cc = null,
@bcc = null,
@subject = 'Change Tracking Insert Failure',
@body = null,
@HTMLBody = @html,
@importance = 1,
@file = null
END
END CATCH
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER DDL_changeTracking_tr ON DATABASE
It inserts the trigger data into:
Code Block
CREATE TABLE audit_tbl (
databaseId int not null,
--auditTime datetime default getDate() not null,
auditTime datetime not null,
loginName varchar(255) not null,
objectName varchar(255) not null,
objectType varchar(25) not null,
eventType varchar(40) not null
)
go
ALTER TABLE audit_tbl ADD CONSTRAINT PK_audit_tbl_databaseId_auditTime_objectName PRIMARY KEY (databaseId, objectName, auditTime)
CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_loginName ON audit_tbl(auditTime, loginName)
CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_objectType ON audit_tbl(auditTime, objectType)
In the same database that I've run this one, I'm running this code to test it:
Code Block
create procedure cow_prc
as select 1
go
drop procedure cow_prc
Occassionally when I run this, I get the following error:
Msg 241, Level 16, State 1, Procedure DDL_changeTracking_tr, Line 42
Conversion failed when converting datetime from character string.
I am completely lost on this. I've had 3 fellow DBAs look at it and they're not sure what's going on with it. I've even tried writing the trigger logic as a CTE which using isDate() to make sure that auditTime actually is a date.
Any insight would be greatly appreciated. Thanks in advance.
View 6 Replies
View Related
Dec 18, 2006
Execute following T-SQL within Queary Analyzer of SQL Server 2000:=======================================DECLARE @dTest DATETIMESET @dTest='2001-1-1 1:1:1:991'SELECT @dTestSET @dTest='2001-1-1 1:1:1:997'SELECT @dTestSET @dTest='2001-1-1 1:1:1:999'SELECT @dTest=======================================You get what?This is my result which is weird:2001-01-01 01:01:01.9902001-01-01 01:01:01.9972001-01-01 01:01:02.000Then what's the reason of this weird problem?
View 3 Replies
View Related
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Dec 6, 2006
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
View 1 Replies
View Related
Sep 24, 2001
I have a dts package that is mysteriously changing back to a previously saved version. Package is saved, and re-opened with saved changes, then moments later opened again to have been reverted back to an older version. Has anyone ran into this before?
The only thing that I can think of is that if a version of the package is left open on some other machine (as the older version0, it may be auto-saving, and reverting back.
View 3 Replies
View Related
Mar 31, 2008
I run a website, so all of this is done remotely, I do not have access to the servers directly.
On my prior host, everything on the DB worked just fine. Had it optimized and running pretty good for the site by adding indexes, etc.
Then I moved to my new faster server (new host) and at first everything seemed to be just fine. However, some users started complaining about speed in spots.
Sure enough when I checked a given page -- WHAM... the page took about 14 seconds to load. Not exactly fast. :)
So I checked to make sure the indexes copied over and sure enough they did. But it was still slow, even in Query Analyzer/MSE.
So I decided to rerun my view script... on a lark... just to see if that affected something. And sure enough it did. The page started running at 1 second or less. OK, I thought, the view just got corrupted or something.
All was fine until the next night when it happened again. I had added 12 new rows to the table (I do this nightly) and it seemed that adding the new rows slowed down the system. Until the view was rebuilt. With that done again, it's worked fine.
But each night now I have to rerun that view.
What gives??? Is there a DB setting or something causing views/indexes to not be maintained?
The prior server was 2000 or 2003. The new one is 2005.
Again, I cannot get access to the box itself except through Management Studio. The host's tech support group is less than useless, treating everyone as incompetent before they help you.
Any hints or solutions would be a big help!
View 8 Replies
View Related
Mar 12, 2008
I'm trying to CAST an integer to a varchar and I keep getting e+006 in my result set. The reason I need to do this is so that I can combine 2 columns to return a string.
Here is my table:
incomeBracket | lowIncome(int) | highIncome(int)
-----------------------------------------------
1 1000001 5000000
2 5000001 10000000
Here is my query:
SELECT CAST(incomeLow AS varchar) + ' - ' + CAST(incomeHigh AS varchar) AS incomeRange
FROM IncomeBracket_Currency
How can I get my result to look like this:
1000001 - 5000000
5000001 - 10000000
Instead of what it is returning now:
1e+006 - 5e+006
5e+006 - 1e+007
The query works fine for values that have a character length of 6 and less.
Thanks in advance.
View 3 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
May 13, 2006
Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest
View 2 Replies
View Related
Mar 11, 2014
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
View 1 Replies
View Related
Sep 7, 2007
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies
View Related
Jan 17, 2008
e.g.
1st March 2005 12:00:00
is showing as
01/03/2005 00:00:00
instead of
01/03/2005
Why does this happen?
View 4 Replies
View Related
Jul 25, 2006
I was developing a data-driven website but I got this error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(I have Sql server 2005 also downloaded before)
Please help me cause any website that has data can not be executed even the quick start tutorial that ships with the ASP.NET 2.0
What should I do?
View 1 Replies
View Related
Nov 14, 2006
Hello and thanks for taking a moment to read this message.I have two colums from which I am taking values. One of which(a bit field), i am running a CASE statement against it to get SQL Server to return a string. The other is just a simple varchar column. My SELECT statement for the columns look like this:
SELECT Case tblDisplayProfile.Approved When 0 then 'Not Approved' when 1 then 'Approved' else 'Not looked at' END AS Approved, tblDisplayProfile.DisplayProfileDesc
These statements do return me something. Now what I want to do is combine (concantenate) the two fields. This is where I have problems. Any suggestions would be greatly appreciated.
Jason
View 13 Replies
View Related
Nov 19, 2006
Why does ado.net produce this error when I kow for a fact that there are no connections to any sql server 2005 database involved - this could and probbaly has had people off looking at the wrong connection strings in their web.configs.
Note:- The connection string we have is to a sql 200 db I just wanted to make that clear.
CheersGegor
Error Message:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
View 8 Replies
View Related
Jun 5, 2008
Hi, I moved a SQL 2000 DB to a SQL 2005 server. When I run my .NET 1.1 or 3.5 website to call a stored procedure it returns the wrong results.The SQL stored proc runs fine when I excute it within SQL Manager but gives a different result when called via .NET The funny thing is I have 3 SQL servers 2000 , 2005 , and another 2005. The same data on each of the databases and the same website. Two of the databases work but the second 2005 database my production server decrements the dates some how. The funny thing is the stroed proc is not returning dates as such but a varchar of the name of the day that's getting decremented. My stored proc is SELECT
Case datepart(dw, DT_DATE)
When 2 Then 'Mon'
When 3 Then 'Tue'
When 4 Then 'Wed'
When 5 Then 'Thu'
When 6 Then 'Fri' End AS [WeekDay],
Sum(VL_HOURS) as Total
FROM ...
WHERE ....
GROUP BY datepart(dw, DT_DATE) and My c# .net code is "SqlHelper.ExecuteReader( connectionstring, storedproc, paramaeters);"using MS.ApplicationBlocksWhat happens that from sql, and 2 of my websites is when week day = 4 it returns "Wed" However on my Production server it returns "Tue" ???
View 4 Replies
View Related
Mar 18, 2004
i have a form which takes in a lot of input...one of them is a date field..am passing all the values to the stored proc and inserting into a table from there...pretty simple...however when the user does not enter any value in the field...its errors out as...
String was not recognized as a valid DateTime.
heres the code in asp.net
myCommand.Parameters.Add(New SqlParameter("@cusbday",SqlDbType.datetime))
myCommand.Parameters("@cusbday").Value = trim(bday.text)
the field is optional....in my stored proc i am setting a default value too
....@cusbday Datetime =NULL,...
i also xplicitly set it to null through code
if @cusbday is null set @cusbday=null
how else do i resolve this...
thanks
View 13 Replies
View Related
Apr 25, 2001
Hello!
MSSQL Server 7.0 SP1
When I open DTS designer for some unknown reason I'm getting error message "Could not create Component Categories manager".
Then under Task I don't have any choices.
Is there a quick way to fix it?
Thanks
Lena
View 1 Replies
View Related
Feb 20, 2001
I was running a DTS package which transfer a complete database, about 2.3GB in size, to another database on the same server. It was necessary for environment change from dev to QA. I baby sat the package till it was rebuilding indexes (90%) complete. This morning I have the following error message on my screen,
[Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location: r:SPHINXNTDBMSqueryqeexecqsxchng.inl:749
Expression: (m_cbPageMac + ulPxvarSize) <=m_pxpktdesc->m_cbPageMax
SPID: 40
ProcessID: 347
I have no idea at this time what caused it. And as far as I can tell there in r drive mapped wither on the serve or my computer ( I was running the DTS package from my machine).Any help is appreciated
View 1 Replies
View Related
Jun 9, 2003
Last night our production server went crazy. The primary (production) database MDF file had size 15Gb and then for some reason the size became 0 (zero) K, So the SQL right away reported the error:
I/O error 38(Reached end of file.) detected during read of page buffer..
Error: 823, Severity: 24, State: 10
And the database became "suspected".
Now we restoring it, but have anybody seen such a thing before? I'm breaking my head to figure out what could cause this problem!!!!!
Dim
View 5 Replies
View Related
Aug 1, 2002
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o(
what maybe the problem?
thanks!
View 4 Replies
View Related
Jul 5, 2004
When I run the command:
exec master..xp_cmdshell 'NET USE'
from the analyzer the box responds there are no entries in the list.
After that, I run the command:
exec master..xp_cmdshell 'NET USE Z: /DELETE'
after which the box responds with a "network connection could not be found."
and that's all okay.
The weird thing is:
exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME'
results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
thanx,
View 5 Replies
View Related
Feb 17, 2006
Hello,
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP).
SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP
but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method? :(
Thanks for the helpers,
BuildHome
View 3 Replies
View Related
Dec 30, 2003
Heres some SQL I am trying to execute:
INSERT INTO Contact_Info (First,Last,Age) VALUES ('Alex','Strait',19)
Now it seems basic, but there in one more field that you're not supposed to specifiy a value in the SQL, its an int IDENTITY datatype field called num_id. Now this SQL works if it doesnt have the num_id field. But heres the error is gives me, I have never seen it before
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
/neosql.asp, line 847
Does anyone know what is wrong?
View 6 Replies
View Related
Jun 13, 2008
Hi
I have this stored procedure that works a little odd, some times it do what I want it to do which is....
- Insert orderinformation in tbl_Torderinfo table and then transfer all rows that are associated with a ordernumber from tbl_Ctemp to tbl_Torders
but sometimes it only insert data into the tbl_Orderinfo table and doesn't transfer the order details from tbl_Ctemp to tbl_TOrders even if there is rows to transfer. Any ideas of what might cause this odd behaivior?
p_AddOrder
@AdressID Int,
@UserID Int,
@UserRealName nVarChar(255),
@Comment nVarChar(4000),
@OurReference nvarchar(255),
@YourReference nvarchar(255),
@DeliveryAdress nvarchar(4000),
@Buyer nvarchar(4000),
@DeliverBy nvarchar(255),
@DestCountry nvarchar(255),
@FreightMark nvarchar(255),
AS
DECLARE @iOrderID Int
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_TOrderInfo
(
AdressID,
UserID,
UserRealName,
DateOrdered,
Comment,
OurReference,
YourReference,
DeliveryAdress,
Buyer,
DeliverBy,
DestCountry,
FreightMark,
OrderID
)
VALUES
(
@AdressID,
@UserID,
@UserRealName,
GETDATE(),
@Comment,
@OurReference,
@YourReference,
@DeliveryAdress,
@Buyer,
@DeliverBy,
@DestCountry,
@FreightMark,
@OrderID
)
SELECT @@IDENTITY
SET @iOrderID = @@IDENTITY
--Insert New ID and Info into Orders table
INSERT INTO tbl_TOrders (TOrderID,ProductName,OwnBID)
SELECT @iOrderID, Product, OwnBID FROM tbl_CTemp WHERE UID = @OrderID
END
View 20 Replies
View Related
Feb 17, 2006
Hello,
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP).
SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP
but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method?
Thanks for the helpers,
BuildHome
View 5 Replies
View Related
Aug 12, 2006
have a dts package that is run by a scheduled job which was runningsuccessfully for several months and suddenly stopped working.The dts package does the following: Truncates the table, does a datapump from a specific excel file to a table on sql server, deletes theexcel file. The job does the following:Step 1: Look to see if a file exists and if so, run the dts package. Onsuccess of this step the job goes to step 2.Step2: Looks to see if any files still exist after the first stepStep3: Failure notification. To be triggered on failure of step 1 or2.The owner of the job is the Account that is used to run theSQLServiceAgent and in the SysAdmin server role.The owner of the job is also in the SysAdmin server role.Both the accounts are Windows Accounts.The job runs at schedule time, finds the file, and runs the dtspackage. The first part of the dts package runs successfully - Thetable is cleared. However it is not doing the data pump part. Thereare no failure notifications. The job then goes to step two and sendsa message that the file still exists. The dts task is set to output anerror file which is not being updated, which tells that the task is notexecuted. If the dts package is run manually, then the package runssuccessfully.Any help would be appreciated.ThanksKR
View 2 Replies
View Related
Aug 31, 2006
I have just installed my server with server2003 enterprise edition, everything is working fine. However, after I make it online for awhile, I found a weird thing on the tool bar just beside the clock. There are 2 hidden icons, when I move the pointer to these 2 icons, it shows the IP address of my server. Just wanna know if there is anything wrong with that.
Thanks!
tom
View 6 Replies
View Related
Aug 29, 2007
I'm seeing some strange behavior from a stored procedure of mine. It essentially grabs a bunch of rows using a fairly simple JOIN....here's the from statement:
Code Snippet
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId,
ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId ASC) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.AccountPaymentId = PY.AccountPaymentId)
INNER JOIN Payee PE ON PE.PayeeId = PY.PayeeId INNER JOIN
Party PT ON PE.PartyId = PT.PartyId INNER JOIN
Distribution DS ON PY.DistributionId = DS.DistributionId LEFT OUTER JOIN
Account AC ON DS.AccountId = AC.AccountId INNER JOIN
clm CM ON PE.clm_no = cm.clm_no LEFT OUTER JOIN
PartyAddress PA ON PY.PartyAddressId = PA.PartyAddressId AND
PT.PartyId = PA.PartyId
WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
and ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (DATEADD(day, DATEDIFF(day, 0, PY.PaymentDate), 0) = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
AND ((@IsReissued IS NULL) OR (PY.ReissuedInd = @IsReissued))
ORDER BY AccountPaymentID ASC
When I pass a 1 for the @IsReconciled parameter, I get the right number of rows back - 9779. But when I pass a 0 (zero), i get no rows back, although there are 222 rows which satisfy the condition.
Is there somethig I'm overlooking (I don't think I am...)? I don't know whay 1 works and 0 wouldn't...
FYI - the @IsReconciled parameter is set to NULL at the outset of the procedure -
@IsReconciled Bit = Null
View 1 Replies
View Related
Jan 30, 2008
Hi, I hope someone can help me with a weird problem I'm having. Towards the end of last week a number of users started reporting problems with one of our legacy applications. The application is written in VB6 connecting via ADO to a SQL 7 database. I managed to trace the error to a piece of inline SQL (shock!horror!) in a DLL that hasn't been changed since 2002, it occurs on the adodb recordset open and generates the error "could not complete cursor operation because the table schema changed after the cursor was declared" although nothing has changed on the database. It does not always happen, it depends on the contents of the strOrganisationIDs and the order of the numbers, although the same combination of numbers will generate the error every time! Initially there were certain users who were not having the error, but they have all gradually 'caught up'! There were some windows updates rolled out about the same time the errors started occuring, but un-installing them makes no difference. Here is a snippet of the code:-
Dim rsRecordSet As ADODB.Recordset
Dim strOrganisationIDs As String
strOrganisationIDs = "(111974,21986,0)"
strSQL = "SELECT o.organisation_id, o.name, o.town, o.county," & _
" o.country, o.key_customer, i.industry_sector," & _
"(SELECT Name FROM SODA_User WHERE User_Id = o.User_ID) as Name1," & _
"(SELECT Name FROM SODA_User WHERE User_ID = o.Assignee) AS Name2," & _
"o.registered_on_web " & _
" FROM Organisation o, Industry_Sector i WHERE o.Sector_ID = i.Sector_ID" & _
" AND Organisation_Id IN " & strOrganisationIDs & _
"ORDER BY o.Search_Name"
rsRecordset.open strSQL
I have managed to find a way around the error - I removed the spaces in front of the o.country, don't know why they were there, and they shouldn't cause the error - in fact they are there on the occasions it works, it seems to be the combination of spaces and certain numbers in the strOrganisationIDs . It sounds stupid and i've been pulling my hair out looking for a reason, as have several colleagues. I don't want to have to roll out the fix as it will be quite messy and affects a lot of users, although i may have to - but what I really want to know is why it has happened. I hope someone can shed some light on this, if any other info is required, let me know.
View 8 Replies
View Related
May 21, 2007
I have an int field in my custom data transform component, it's increased by 1 each time the data flow is executed, following is the code snippet I used:
Code Snippet
// fields used
bool isIncreased = false;
int field = 0;
// in Validate() method, after all validation passes:
if (!isIncreased)
{
field++;
isIncreased = true;
}
// in Cleanup() method:
isIncreased = false;
But each time I open the SSIS project in Business Intelligence Development Studio or change the ConnectionManager, the int field will be increased twice, and after that, each execution of the data flow will only increase the field by 1(which is correct). Does anyone encountered this problem before? I don't understand why it's increased twice in the situations I described.
View 11 Replies
View Related
