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.
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.
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.
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)).
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.
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."
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.
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
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)
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)
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
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.
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
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.
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
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..
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.
Thanks,
Arif
Server Error in '/' Application. --------------------------------------------------------------------------------
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Msg 102, Sev 15, State 1, Line 134 : Incorrect syntax near 'ge'. [SQLSTATE 42000]
hi
I have a script which worked fine in SQL SERVER 2000. We have now upgraded to SQL SERVER 2005 (SP1). When I run the script from Query Analyser it works fine. But when I schedule the same script to run as a job, it fails and i get the following error:
Msg 102, Sev 15, State 1, Line 134 : Incorrect syntax near 'ge'. [SQLSTATE 42000]
Now, there is no problem with the syntax. What could be the problem?? I have been searching for a logical answer on the Internet and although other people seem to have encontered a similar problem, I have yet to find an answer.
Hi,I have a Scheduled Job on my PC that runs against a Linked Server. Ihave scheduled the Job to run when SQL Server Agent starts and I haveconfigured my SQL Server and SQL Server Agent to start when the OSstarts. The connection to the Linked Server is via my Broadbandinternet connection.I have some suspicions as to the cause and hence a possible solutionbut I want to run my ideas past anyone else who may have experiencedthe same.1. Should I have my DSL modem turned on before I start the PC so thatthe Network connection is available when the OS starts?2. Are the Network connections only made available after you havelogged in and hence not established quickly enough before SQL Serverand SQL Server Agent are started?Yesterday, when I rebooted the PC with the modem still on theScheduled Job ran without failure but this morning again it failed. Myusual routine is to turn on the modem just before I login.If No. 2 is the case then maybe I just need to set the "Retryattempts" parameter.Thanks,Michael HolbertonHospedaje Los Jardines & Sacred Valley Mountain Bike ToursCusco Database Development and Cycling Serviceshttp://www.machawasi.com/http://machawasi.blogspot.com/http://databaseservices.blogspot.comhttp://serviciosdeciclismodelperu.googlepages.com
Problem Restoring Backup database in MSSQL 2000.I made a backup of one of my databases from one server and now I'm trying to restore to the production server (Clustering) using Enterprise Manager. I remote the server - launched Enterprise Manager and I select the restore from disk and I give the full path of a local drive in the server where the backup is located - when I clicked "ok" it first tells me: "Unable to verify the existence of the backup file location. Do you want to use the backup file location anyway?" If I answer "Yes" and "OK" it presents a error msg: "Cannpt open backup device 'c:.......bak. Restore database terminating - Microsoft SQL-DMO(ODBC SQLSTATE 42000). I have moved this backup around and I still get the same msg. Can anybody help - need assistance ASAP - thank you
I'm currently receiving the following error message whilst attempting to install SQL Server 2005 Standard Edition on Windows Server 2003 (32 Bit): Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.
This server already has an install of SQL Server 2000 as the default instance. I'm attempting to install a new named instance of SQL Server 2005.
Extract from log:
<Func Name='LaunchFunction'> Function=Do_sqlPerfmon2 <Func Name='GetCAContext'> <EndFunc Name='GetCAContext' Return='T' GetLastError='0'> Doing Action: Do_sqlPerfmon2 PerfTime Start: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 <Func Name='Do_sqlPerfmon2'> <EndFunc Name='Do_sqlPerfmon2' Return='0' GetLastError='2'> PerfTime Stop: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 MSI (s) (4C:FC) [10:20:02:833]: Executing op: ActionStart(Name=Rollback_Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Removing performance counters,) <EndFunc Name='LaunchFunction' Return='0' GetLastError='0'> MSI (s) (4C:FC) [10:20:02:849]: Executing op: CustomActionSchedule(Action=Rollback_Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1281,Source=BinaryData,Target=Rollback_Do_sqlPerfmon2,CustomActionData=100Removing performance counters200000DTSPipelineC:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.INI) MSI (s) (4C:FC) [10:20:02:849]: Executing op: ActionStart(Name=Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Installing performance counters,) MSI (s) (4C:FC) [10:20:02:849]: Executing op: CustomActionSchedule(Action=Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1025,Source=BinaryData,Target=Do_sqlPerfmon2,CustomActionData=100Installing performance counters200000C:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.INIC:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.HC:Program FilesMicrosoft SQL Server90DTSBinnDTSPipelinePerf.dllDTSPipeline0DTSPipelinePrfData_OpenPrfData_CollectPrfData_Close) MSI (s) (4C:94) [10:20:02:864]: Invoking remote custom action. DLL: C:WINDOWSInstallerMSI1683.tmp, Entrypoint: Do_sqlPerfmon2 <Func Name='LaunchFunction'> Function=Do_sqlPerfmon2 <Func Name='GetCAContext'> <EndFunc Name='GetCAContext' Return='T' GetLastError='0'> Doing Action: Do_sqlPerfmon2 PerfTime Start: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 <Func Name='Do_sqlPerfmon2'> <EndFunc Name='Do_sqlPerfmon2' Return='2' GetLastError='2'> PerfTime Stop: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 Gathering darwin properties for failure handling. Error Code: 2 MSI (s) (4C!F0) [10:23:46:381]: Product: Microsoft SQL Server 2005 Integration Services -- Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.
You can ignore this and it will complete the installation, but subsequently trying to patch with SP2 will fail on the same sections - Hotfix.exe crashes whilst attempting to patch Database Services, Integration Services and Client Components (3 separate crashes).
I've removed SQL Server 2005 elements and tried to re-install, but it's not improved the situation.
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
I cannot execute a package by using Execute Package task. I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says
Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
We have reports deployed in the Report Server. While connecting from client, we are getting the error "An internal error occurred on the report server. See the error log for more details. rsInternal Error)"
Then we went to Report Server, Reporting Service and SQL Server service are all are running fine.
Important thing is some time the reports are working fine, sometimes i am receiving this error. Please help.
We predict whether the services are automatically restarted or transaction logs exceeding the limit or any other parameters to set to avaoid this error?
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?> <ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
I have the error above, but no error log. I can preview the sub report - but this main report fails after working this morning. This is for internal company reports and I rebuilt this one after converting from access. I have looked where the error logs should be, but there are no error logs. I rebuilt the query as I needed to change this, but this did not help. Is there someone who could point me in the correct direction.
