Hi,
When i m trying to retrieve data from a table named DAY1(USDCHF1998) , i get this error saying " Sql state 42000, sql error code:155 'USDCHF1998' is not a recognized OPTIMIZER LOCK HINT option ".
But its OK, when i use the table as DAY1USDCHF1998
Hi guys i used maintanance wizards to schedule, transactional log backups it gave me this error SQLMAINT.EXE Process exit code 1.
I went to job history and i get this error code SQL STATE 42000 error 22029.
if any one has hints on how to deal with this i will appreciate
hello,
we have changed the name of MS SQL server 2005 from XYZ to ABC using
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error
Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)
Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. Unable to determine if the owner (XYZSQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZSQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).
please help us in this issue
Hi Guys:
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
Error :
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
Thank you.
Hello, I get the following error when I run my package interactively. From the logs written out by the driver, it appears that all is working well as far as connecting to the data source and pulling data. It seems as if this error occurs when the DataReader source tries to process the received data.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.Data.Odbc.OdbcException: ERROR [42000] XML parse error at 162:1338: not well-formed (invalid token)
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed the pre-execute phase and returned error code 0x80131937.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (691)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "MyPackage.dtsx" finished: Success.
I am not sure where to look next. Any help is much appreciated.
Dave
I have three instances of sql server 2000 on three boxes.
WE collect the data on box1 and box2 and copy that data onto box3. We have couple of stored procedures whhich copy the data from box2 and box1 onto box3. It was working fine for the last few monts. But from last 4 hours the job is failed and its giving the following message
Executed as user: abcdservicestart. SQL Server does not exist or access denied.
[SQLSTATE 42000] (Error 17). The step failed.
But the sqlserver exists on all the 3 boxes and they are connected and even we haven't changed any login info on all the 3 boxes. What could be the reason for the above error.
Thanks.
I keep getting this error:
OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000
I do not want to add the login account to sysadmin role on my production server and giving the logon account db_reader rights to my database is not working. Is there another way around it.
I'm not sure if this is the correct group for this messages, but here it is anyway.
I have a job that has 3 steps to, periodicly the job errors out on Step 1. Following is the message (from Job History).
--------------------
Executed as user: SMIsqladmin. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300). The step failed.
--------------------
Can someone please explain this message, I have no idea how to fix it or what the cause is.
Thanks
David Davis
I am having a problem with a Job Step that is failing. The actual Step runs
a Stored Procedure, but the Job keeps failing and returning the following
error
"sqlmaint.exe failed. [SQLSTATE 42000](Error 22029). The step failed."
Does anyone know what this means ?
Thanks.
during maintenance plan i get following error :
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'AJAX'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[8] Database AJAX: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
Hello,
I'm having trouble running jobs with my active directory (ADS) account. I've setup my SQL services to run under an ADS account, but jobs cannot seem to query ADS for user information. We're running Windows Server 2003 and SQL Server 2005 SP2.
Here is the error message:
==
The job failed. Unable to determine if the owner (ADSme) of job eFASRtest has server access (reason: Could not obtain information about Windows NT group/user 'ADSme', error code 0x5. [SQLSTATE 42000] (Error 15404)).
==
also this message in log:
==
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'ADSme, error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
==
I already tested the suggested:
execute as login='adsme' and I get the same error on both (my local installations and production)
appreciate your help
(SQL SERVER 2000)
I keep getting the "primary filegroup is full" error when ever I try to write data even though the following things are true:
allow unlimited growth set for BOTH database and transaction log
auto shrink and auto update statistics flags are set in database options
growth set to 10 percent for both
database is 4GB but disk has 12GB freespace
transaction log is on same disk but is only 8MB ???
SQL shows the database has 2% free space but any attempts to write cause the error, shrinking drops this to 1% but still produces the same error on writes
SQL shows the transaction log has 90% free space
backup maintenance plans are in operation for both transaction log and database (write to different drive)
I am completely confused!
Hi all,
I am getting error message 'An internal consistency error occured sqlstate:42000" when i am trying to restore the database.
Regards,
Shashi.
We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley
Hi,
This is my first posting and actually I have big problem which I need to resolve immediately:
I have a 38 GB Database on my SQL Server which I want to full-backup. Until now backup was created on the same volume where the database resides. But now the volume is out of disk space. So what we did is that we connected an external USB disk drive with 500 GB to the SQL Server and try to backup there.
But our first and all consecutive backup attemps failed with error SQL-DMO (ODBC SQLState: 42000). It's working fine the first few minutes as we can see that the backup file is continously growing. But then it abruptly aborts with above error message.
Can anyone figure out why ? Any help is appreciated.
Thx
Overlord1970
Good morning all. I need some help with a stored proc that is driving me up a wall. It's probably something stairing me right in the face but I can't see it!
I keep getting the following error on the procedure that I'm working on:
SQL-DMO (ODBC SQLSTATE: 42000)
ERROR 156: Incorrect syntax near keyword 'AS'
Must declare the variable '@signid'
Must declare the variable '@signid'
Here's the code:
CREATE PROCEDURE [dbo].[ws_savesignature2db]
@xml as text='',
@image as image='',
@imageformat as varchar(20)='',
@imagename as varchar(40)='',
@imagesize as int=0,
AS
insert into signaturetable
([image], imageformat, imagename, imagesize)
values
(@image, @imageformat,@imagename,@imagesize)
/*
if your XML field datatype is Text or nTEXT, use the code below in Sql Server 2000.
If you use Sql Server 2005, you can use varchar(max) or nvarchar(max) which will be
much easier and you do not need the code.
*/
select @signid = @@IDENTITY
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR([xml])
FROM ws_signature
WHERE signid= @signid
if @ptrval is not null
WRITETEXT ws_signature.xml @ptrval @xml
GO
Any help you can give would be a life saver!!!!
Thanks in advance.
Jim
Hi,
I run 2 steps viz with NORECOVERY and with RECOVERY in sequence as SQP jobs. The 2nd step fails some times. When it starts failing, I run full backup of the production server again and the 2nd step starts succeding. Unless I run the full backup the 2nd step keeps on failing.
What are the conditions which generate for [SQLSTATE 42000] (Error 3013) and how to tackle this problem.?
The 2 steps are :-
1.RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH
Replace ,
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
NORECOVERY
GO
2.RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionSQL daily diff back up'
WITH
Recovery
The status of the 2 steps is :
1 Executed as user: BPOTESTREMEDY
emedybackup. Processed 104480 pages for database 'ARSYSTEM', file 'ARSystem' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'ARSYSTEM', file 'ARSystem_log' on file 1. [SQLSTATE 01000] (Message 4035) RESTORE DATABASE successfully processed 104481 pages in 49.452 seconds (17.307 MB/sec). [SQLSTATE 01000] (Message 3014)
2. Cannot apply the backup on device 'E:SQL backup from productionSQL daily diff back up' to database 'ARSYSTEM'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
I get the following error when I try to run my scheduled job.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Any ideas Thanks in advance
Reddy
I have log shipping set up between 2 SQL 2000 SP1 Servers on Win 2000. The db is small 10 meg, and when the restore job on the backup server fails I am getting "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." as the message, the maint paln on the primary server show no error. Anyone seen this before? The restore has worked 3 out of 5 times
View 2 Replies View RelatedHi all help needed from your End.
I have a re-indexing stored procedure,yester day night it got failed
"Executed as user: CONNECTSRVmyadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
what may be the reason..
Hi,
I have three publications on a dev server. All are sql 2005 merge replication (push) with replication running to subscribers on the same instance of sql server (note this is just dev testing).
However, I have just marked one for re-initialisation / create new snapshot / don't upload changes but when I kick off the synchronisation it says it has insufficient permission to run the snapshot.
When I do this for the other two there is no problem.
SQLAgent is running (using LocalSystem) so I don't know why my reinitialisation won't work.
Any help would be greatly appreciated.
Our SQL Server 200 box is getting perflib errors when we get a decentamount of people using an application that I wrote, call queue system,web based. To accomplish a queue type system on a button push I wrotea query like this...BEGIN TRANSACTION;Select top 1...fields here...FROM table with (xlock,readpast)(2 joins)WHERE numerous where clausesORDER BY 2 order bys.Now our sql server starts timing out..then in the app log this showsup....Error: 1203, Severity: 20, State: 1Process ID 62 attempting to unlock unowned resource PAG: 6:1:126407.For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/events.asp.The reason I am doing xlock is to make a record not viewable to 2people if they click the button on the web form that runs the abovequery within the same minute, they would get different records....So to avoid this error which I assume is due to my xlock should Irethink my query?
View 3 Replies View RelatedI have a big problem with a SQL 6.5 (no SP) running
in a production environment on NT 3.51 SP 4 since
2,5 years.
Suddenly Friday at 99/10/01 14:17:38.93 we started
having problems and we could'nt connect to the
DB from our client server application written in VB4.
We rebooted the server and we had other errors at
99/10/01 16:15:40.96.
The error code is 605-21-1 and it seems like the main
table (FILMS) of our db (VISPO) has phisical allocation
problems.
here is en extract from the SQLServer error log
of the first problem:
99/10/01 14:17:38.93 kernel udread: Operating system error 23(Data error (cyclic redundancy check)) on device 'E:MSSQLDATAvispodat.DAT' (virtpage 0x05000f35).
99/10/01 14:17:43.62 spid13 Buffer 9531a0 from database 'vispo' has page number -1 in the page header and page number 3893 in the buffer header
99/10/01 14:17:43.69 kernel mirrorproc: i/o error on primary device 'E:MSSQLDATAvispodat.DAT'
And here is what we had at 16:15
99/10/01 16:15:40.96 spid11 Getpage: bstat=0x1008/0, sstat=0x80010130, disk99/10/01 16:15:40.96 spid11 pageno is/should be:objid is/should be:99/10/01 16:15:40.96 spid11 0xffffffff(-1)0xffffffff(-1)99/10/01 16:15:40.96 spid11 0xf34(3892)0xe4e2b2e(240003886)99/10/01 16:15:40.96 spid11 ... retry bufget after purging bp 0x943a8099/10/01 16:15:40.98 spid11 Error : 605, Severity: 21, State: 1
99/10/01 16:15:40.98 spid11 Attempt to fetch logical page 3892 in database 'vispo' belongs to object '-1', not to object 'FILMS'.
We are thinking about restoring the BackUp of the previous night that should be OK,
but I would like what we can try to do in order to have the
db working properly.
Even if the restore would be fine, maybe we are starting
to have problems on the disk subsystems?
What can we check, something like scandisk in DOS?
I attach here in ZIP format the 2 log files and plese ignore the MAPI errors
that are "normal", we just don't care.
I don't know if it's possible to attach files to a newsgroup or a discussion
group but I will try
Any help would be appreciated.
Thanks in advance.
Eugenio La Mesa
Publisoft
Rome - Italy
Please help me the cause and solution for Error: 602, Severity: 21, State: 4 which is raised while trying to excute a select query on a particular table.
This does not occur every time i excute the query
thanks in advance
Attempt to fetch logical page ' ' in database ' '. Object belongs to ' ' not object ' '.
Please someone help me fix this. My table is corrupt and I cannot drop it and restore it because I keep getting an error message that says:
Cannot drop table ' ' there is not enough space in syslogs. blah, blah, blah.
Can anyone help me? It woould be much appreciated.
Christine
I synch 4 databases everyday and on one of them I get the Error 823, Severity 24, State 2 error (Yes I know you have heard this before but I am a new, LOL). I have tried detaching the database and copying it, renaming that database and then copying back to the original name with the same error; therefore, I think the error is somewhere in the database structure. If it is the database structure would it still give the error above or is that simply a hardware error? I have been searching for solution and I am at a loss. Any help would be appreciated.
Thanks.
N. Adkins
Hi All
Ignore the error below, It's got to do with isolation levels (nolock in this case): refer http://support.microsoft.com/?kbid=834290 for details.
Error: 644, Severity: 21, State: 6
Could not find the index entry for RID '1631393920202020202020202020202033392020202020202 020202020202019bcd3113230303037333120202020202020' in index page (1:420134), index ID 0, database 'DBName'..
What I'd like to know is how to get the table name that was affected out of this error message. Do I query sysindexes, sysobjects, use a function or something else?
Thanks in advance.
Graham
Hi, i encounter a problem with my sql.
What my system does is that it restore/dump database from some backup.
it will then send email using the xp_sendmail. After that it will send out sms using a command line script.
The timing as per the attachment i put in here. Pls kindly help. production issue.
Here are some other events that caused the error
Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQLlogSQLDump0003.txt
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Stack Signature for the dump is 0xF107CBA8
Pls let me know if you need any information
Login failed for user ''. Reason: Failed to open the explicitly specified database. [CLIENT:xx.xx.xx.xx]
No missing database & having sysadmin permission.
why this error coming?
Hello guys,
Error: 18456, Severity: 14, State: 8.
Login failed for user 'sa'. [CLIENT: <ip address>]
With what Ive searched.. State: 8 is wrong password. What Ive
encountered I believed is not State: 8 I guess... I have 2 pc..
PCX and PCY.. PCY has SQL Server 2005 so does PCX.. when I run my application that stores data to PCY DB it prompts me an error just
like the one above.. but what seems to be confusing me is both of them have the same DB password. Did I miss something... please help..
-------------------
Real Programmer dont document
If its hard to write Then
its easy to undertand.
End If
I am writing a user defined function but I am receiving an error message that I can figure out. Any answers out there?
Msg 102, Level 15, State 1, Procedure ufn_GetStreetNumber, Line 25
Incorrect syntax near '@StreetNum'.
-- ---------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Parses out the Street number for Address field
-- =============================================
CREATE FUNCTION dbo.ufn_GetStreetNumber
(
-- Add the parameters for the function here
@StreetAddr varchar(50)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @StreetNum varchar(50);
DECLARE @iIter smallint, @iNoOfChars smallint, @iStreetNo int;
SET @StreetNum=NULL;
SET @iStreetNo=0;
SET @iNoOfChars=1;
SET @iIter=1;
-- Add the T-SQL statements to compute the return value here
WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1
@StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)
@iIter=@iIter+1
BREAK
@iStreetNo=@StreetNum
-- Return the result of the function
RETURN @iStreetNo
END
GO
Hi,
In my application i used to do the take the backup of current
database and restore it with a backup which was taken earlier and
execute a select command on a particular table.
My problem is,backup & restore operations get completed
successfully but error is raised when tried to open a recordset
for a select statement.Following error was recorded in the sql
error log.
Error: 602, Severity: 21, State: 4
Could not find row in sysindexes for database ID 15, object ID
1109578991, index ID 1. Run DBCC CHECKTABLE on sysindexes..
After this i terminated my application and repeated the above
operation this time i got the same error but with index ID 0 but
the database id and object id remained the same.
When scanned the sysobjects for the particular objectid and i
found out that the id belong to a SP - dt_setpropertybyid_u
I have encountered the same problem three times,but after that i
did not get the problem
Regards