Configuration : MSSQL2005 SP2 on Windows 2003 server
I have a strange issue when scheduling SSIS Job.
When I schedule it, I set the owner as the domain administrator. The scheduled job can be run manually without any problem. The scheduled job run automatically at scheduled time wihtout problem.
But , for an unknow reason, after some cycle, the job failed with the following error :
The job failed. The owner () of <name of the job> does not have server access
As you can see, the owner name is blank.
If I look to the job settings : the owner is still the domain administrator.
If I try to run it manually, the job fails with the same error.
now if I just rename the job name from <name of the job> to <name of the job2> then all problems are solved.
If we let the job failing everyday, sometimes, it starts again to work properly.
We have this issue on 2 jobs.
If anyone has faced this issue : thank you for advise.
We recently converted an application from accecc97 to access2003. We used to have 2 databases : 1 with all and only the data (axelD.mbd), 1 with all the forms, query's, ... (axelP.mdb)
We replaced the data (axelD.mdb) with an MSDE database, through UPSIZE and so on. That all worked fine. This database is placed on the dataserver (DATA1), the converted programm is on the FRONT2 server (FRONT1 stil usess access97 until conversion is totally ready).
We are able to connect to the MSDE by using ADODB recordsets. We are also perfect able to link the MSDEtables in our axelP.mbd database
We started to test some heavier parts of our application and started to get the following error:
"ERROR 3151" "ODBC--connection to 'JOROSOFT' failed"
Nothing more.
This happened in a quite complex routine, whitch updates a lot of records, several times and so on. We could reproduce the same error when executing the following code:
Public Function TestBestellingen() Dim sql As String Dim intervalset As DAO.Recordset Dim planset As DAO.Recordset Dim tellerke
x = Opendatabases() tellerke = 0 sql = "Select bestelnummer from vkpbestellijn where bestelnummer > 100000" Set planset = Db_AxelP.OpenRecordset(sql, dbOpenDynaset) Do While Not planset.EOF tellerke = tellerke + 1 Debug.Print tellerke & " - " & planset!Bestelnummer & " --> " & IsKlantStof(planset!Bestelnummer) planset.MoveNext Loop End Function
Function IsKlantStof(p_bestelnummer) Dim sql As String, rst As DAO.Recordset Dim hulp_klantstof As Boolean
hulp_klantstof = False x = Opendatabases() sql = "select klantstof from vkpbestellijn where bestelnummer = " & p_bestelnummer Set rst = Db_AxelP.OpenRecordset(sql, dbOpenSnapshot)
With rst Do While Not .EOF If !KlantStof = True Then hulp_klantstof = True .MoveLast End If .MoveNext Loop End With rst.Close Set rst = Nothing IsKlantStof = hulp_klantstof End Function
Function Opendatabases() If Db_AxelP Is Nothing Then Set Db_AxelP = DBEngine(0)(0) End If End Function I know this code makes no sence in a real-time environment, but it was to reproduce the error. This code basically runs trough a 30.000 records and does a little check for certain fields After 1981 records we get the above error. (sometimes after 1979 or 1980 or 1982 records)
"vkpbestellijn" is a table that is linked in the AxelP.mdb trough an ODBC connection. We get the same error (and after 1981 times) if I change the where to a totally other range of "bestelnummer"
I have installed the latest patches of Jet 4.0, MSDE-server, ...
I've been searching the internet for abour 3 days now, and just cant even find the smallest clue what could make this happen.
I hope someone here has an idea. If you need more details, just ask.
tia, Axel.
P.S. I'm from Belgium, so my english aint "correct to the point".
We are running an ASP application. That is fairly simple.It selects from one table (15.000 records), and updates into another table.But when we execute the ASP script, it reports the following error, aftercompleting between 70%-80%. This varies each time it runs"Microsoft OLE DB Provider for SQL Server error '80004005'[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or accessdenied. "The application is running in ths following server environmentServer 1Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)Windows 2003 Web Server (IIS)Server 2Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)Windows 2003 Standart serverMicrosoft SQL Server 2000
I have posted this in the SQL Server Data Access forum but wondered if this was a better location.
We have two Server 2005 boxes and one Sever 2000. About 3 months ago we started experiencing random database connection drops. These were manifested by exception errors in database applictions that have worked fine for years. The .NET application exception message: "The specified network name is no longer available" The Delphi application exception message: "Possible network error. Write to SQL Server Failed." Server Management Studio also started giving the error: "Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)" All three servers randomly lose connections. All our clients randomly lose connections. I don't think its a network problem because only db applications seem to be affected. Also, a client can be running two or more db applications. Application A will fail, while B marches on unaffected.
Does anyone have any ideas what could be causing this? I would like a way to monitor the SQL Server connections but am not sure how. sys.sysprocesses seems to show all the connections, but it doesn't show when a connection is being terminated. SQL Server log shows no connection messages.
Excuse me for the 'noobish' question but it seems in my mining models, i am 'loosing' input variables. I am using the Microsoft Decision Tree algorithm and eventhough i have set 4 variables as 'input' and all 4 of them are in my mining structure, the model is using only 3. That 4th variable is also missing from the dependency network graph. Can anyone help me solve this problem?
If this is not best forum to ask this question on, please advise. We have two Server 2005 boxes and one Sever 2000. About 3 months ago we started experiencing random database connection drops. These were manifested by exception errors in database applictions that had worked fine for years. The exception error stated that no db connection exists even though it had been fine a moment ago. Server Management Studio also started giving the error: "Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)" All three servers randomly lose connections. All our clients randomly lose connections. I don't think its a network problem because only db applications seem to be affected. Also, a client can be running two db applications. Application A will fail, while B marches on unaffected.
Does anyone have any ideas what could be causing this? I would like a way to monitor the SQL Server connections but am not sure how. sys.sysprocesses seems to show all the connections, but it doesn't show when a connection is being terminated. SQL Server log shows no connection messages.
for some reason my Sql Server has stopped and i am not able to restart it at all, do i have to reinstall the complete SQl Software or is there ne way i can start the MSSQL Server
I'm getting a connection and then loosing my connection upon refreshing the browser with this script connecting to MSSQL using php, when trying the following:
PHP Code:
$connection = mssql_connect("127.0.0.1","test","") or die("Could not connect mssql db on " .$config['dbhost']);
mssql_select_db("dbName") or die("Could not select database " ."dbName");
Are their other ways to see more error handling in connecting to MSSQL
I'm using varchar as a datatype and my leading zeros are chopped-off once ther data reaches my Stored Proc. The table will allow me to store the values with leading zeros if I enter them manually, but I cannot insert them via ASP/StoredProc.
Hi,I'm having an issue where users are copying and pasting text from word documents. The formatting of certain special characters such as mswrods curly ' is fine until the string is inserted in to the db (sql2000 varchar field). I'm using asp.net 2.Any ideas on this one?Ta Dan
I need to copy the table structures from my production database to development database but not loose the data in developement. Is there a way to achive this by creating some scripts. thanks
There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @@IDENTITY function to update FOREIGN KEY of some table.
When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @@IDENTITY function does not actually shows just inserted row's identity value.
This issue also generated when performing inserts via ADO.
When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.
- With low volumnes of data, less that 50,000 rows, no problems
- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.
But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.
I have a script and on one of the tables I keep loosing the mapping of the last 4 fields. If I go into the task it will ask me if I want it to automatically fix it and I say yes and remap fields but when I run it it bombs and comes back and when I go back into it it tells me there is a problem with same fields. It also rearanges the order and puts these fields last.
I have tried deleting and redoing the transformation only to have the same thing happen. Almost seems like a bug in software. I have seen this before but usually when I fix it it does not return.
i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.
the problem is when this numeric field is > 0 it looses the precision on its decimal value.
example, if numeric value is 0.32
after converting this to a string, the new value will be : .32
it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.
when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.
I created a custom transform that has a custom interface and is a wizard that uses a web service. It creates custom properties and output columns on the fly. I set the dialog result to Ok and close at the end of the steps. The transform then has the custom fields and output columns I created in the wizard. I've verified this by right clicking on the transform and going to the advanced editor. If I then immediately run the package, the custom fields don't exist in the CustomPropertiesCollection. If I close the package and reopen it, the properties now are gone. If I then go through the wizard again, thus recreating the properties, they stay and don't disappear. The quickest way to get a working transform is to add it to my data flow then save, close and reopen the package and then go through the wizard. Just saving after I add the transform does not help.
Does anyone know what might be causing this very strange problem?
I experienced this problems on both Windows Mobile 2003 SE and Windows Mobile 5.0.
Its native development (c++, oledb, atl and mfc).
It's quite simple to reproduce...
1. open a database
2. open a rowset on table A (whatever, valid of course and with both IOpenRowset and ICommandText), read datas and close rowset
3. power off
4. power on
5. try step 2 with another table (failed on openrowset with error 0x80004005) or try table A (sometimes working because of cached memory, sometims failed on Read Datas).
6. being stuck ;-)
Our work-around was, in case we loose our connection (identified by error 0x80004005 on openrowset), we close it and re-open database... ugly for sure, but working.
What I'm looking now is to use some kind of "detection method" like what people in .Net develoment are using "if ConnectionState.Open <> ...) for reopening my database only on demand...
I hope somebody can give me a clue for this problem. Our SQL Server was installed with default option. The SQL Server account is .SQL-Cmdexec. I tried to create two backup jobs to save daily backup files into another server. The SQL Server could not detect that server drive during the database backup set up. I knew that was because the SQL Agent account could not access to that server. I had the following questions:
1. How to grant write right to current SQL Agent account to another backup server?
2. It looks like .SQL-cmdexec is created by SQL Server default installation. If I try to change this account to another account, the Enterprise Manager is the only place to be changed or there is another place keeps SQL Agent account info.
3. If I try to give SQL Agent account access to the other servers, what is the relationship between NT User manager and SQL Agent accounts.
Hello,I have the following error message in the sqlagent.out file, loopingeach minute.2005-12-19 10:58:54 - ! [298] SQLServer Error: 14262, The specified@job_id ('254D5C3B-CB1F-4B02-AD79-FF5AFE343E3B') does not exist.[SQLSTATE 42000] (ConnExecuteCachableOp)I restarted the sqlagent and ms-sqlserver service, but did not fix theproblem.ThanksDiane Lavoie
I have two questions:(1)After I stopped SQLAgent, I couldn't start it up, even restart theserver.Why?(2) I may need to reinstall the server, if I do, do I need touninstall? or just override the current one?Thanks!Saiyou
SQL Server name: BOProd Domain: BODomain I have an NT User 'SQLAgent' with domain Admin previleges. SQL server has 'SQLAgent' user with NT authentication and system admin previledges. I am in process of setting up mail services for sqlmail and sql agent mail. As a part of this process, when I log on to the server computer as SQLAgent, it doesn't let me access Enterprise manager saying 'Login could not be established to BOProd-Login failed for user 'BODomainSQLAgent'. It does allow me to connect to query analyser by providing 'user name/password'.
Any idea why connection to enterprise manager fails?
The SQLAgent service was modified to login as a domain account. SQLAgent would not start. I made sure that the account was in the Admin group on the server, I setup the account as a System Administrator in SQL. I made sure that the network protocols in Client Network Utiliity and Server Network Utility where the same as other servers using the domain account. The message I'm getting is...
SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).
I modified the properties for SQLAgent back to login as local account and I still get the same problem.
Hi all, I have some trouble with SQLAgent...when I try to schedule a jobthat invokes a script (vbs, java, cmdshell,...), SQLAgent crashes at themoment of scheduled execution. If I try to execute it manually, all worksfine. This installation runs on a cluster Win2003 and the node initiate afailover if I don't delete the job and then restart Agent (I can't start theservice before deleting that job!!!). Event viewer logs something like:"LOG] Exception 5 caught at line 191 of file ..src efreshr.c.SQLServerAgent initiating self-termination".I've already tried to debug the service but my dump did not help me.Any suggestion?Thankscamau
Can someone explain me this error I encountered, is this an application error or script error? How can i resolve this one?
"The description for Event ID ( 208 ) in Source ( SQLAgent$TSESMSDBS ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: [0230] GET MA_SMSCODE, 0x523D92CBAACD304E88CF720580B18357, Failed, 2007-11-10 02:30:00, The job failed. The Job was invoked by Schedule 2 (MA_SMSCODE - Every 2.30AM). The last step to run was step 5 (NSCSI - MODEL SMS SHORT CODE).."
Hope to hear from anyone as soon as possible. Thanks.
I have a SQLAgent job created using isqlw to run the query and output the results to a file on the C drive. However, the job never completes, which is strange since the database is fairly new and when I run the query manually in a isqlw window, it completes and outputs the file in seconds.
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
I'm going to be setting up SQLAgent to automatically send notifications on failure. My question is are there any "gotcha's" I might need to no about when doing this in a clustered environment? If the server were to failover, does the agent peform as it should?
On my account we have very separate authority between the NT system administrators and the SQL DBAs. Routinely in SQL 6.5 we would remove sa authority from the NT administrators group and add this authority to a NT database administrators group which we were members of using SQL Security manager. We have tried to apply this same method to our implementation of SQL 7.0. We removed the sysadmin role from the BUILTINAdministrators group and added sysadmin to the DatabaseAdmin NT group which the DBAs and SQLAGENT account are members of. This appeared to work fine until we tried to run our database maintenance plans in SQLAGENT. When sysadmin role is revoked from BUILTINAdministrators the jobs in SQLAGENT using xp_sqlmaint fail with "select permission denied to sysdbmaintplans in database 'msdb' owner 'dbo'"
The SQLAGENT login is part of an NT group that has been granted sysadmin role in SQL Server. This should grant full access to SQLAGENT. What's up? Is the xp_sqlmaint using hardcoded security of some kind? I am confused. Any suggestions? We really need to revoke sysadmin from the BUILTINAdministrators group.
I have several target servers defined, i have a job that needs to run in a serial fashion (ie.. 1 target at at time) if every target kicks off the job at the same time it's going to bog down the disk drives... can this be serialized? or do i need to write a special routine...