 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / September 2005Very Very Urgent, Please Help
Recently we have migrated the datawarehouse server to windows 2003 server from windows 2000 server. Since then all dts packages are getting timedout with the following error. Step Error Source: Microsoft OLE DB Provider for SQL Server Step Error Description:Timeout expired Step Error code: 80004005 Step Error Help File: Step Error Help Context ID:0 The new server is very high end server which has 8 processors and 12GB RAM, and All dts packages are having simple deletes and data transfer tasks. Daily updatestats was running during off hours and proper indices are there. Ran the checkdb on all dbs, found no errors. I never seen these kind of erros on windows2000 and sql 2000 ee sp3. Is there any reason that sql2000 EE SP3 is not fully supported by Windows 2003 server. I have verified the query timeout settings on source and destination server which is 0 (unlimited) Please let me know if settings need to be checked. (Migration : dettahed the databases from windows 2000 server and sql200 EE sp3 and attached to the windows2003 server and Sql 200 EE sp3) If any queries that were run from query analyzer are failing with the following error afetr few minutes : [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. Connection Broken Info : No new logins, all jobs are using "sa" login. Recreated the jobs and dts packages on the new server. Only user databases were dettached and attached and ran the checkdb against all dbs found no errors. > Recently we have migrated the datawarehouse server to windows 2003 server > from windows 2000 server. [quoted text clipped - 33 lines] > General network error. Check your network documentation. > Connection Broken can you create a new empty package and create a new connection to the database? have you validated the connections in your packages? > Recently we have migrated the datawarehouse server to windows 2003 server > from windows 2000 server. [quoted text clipped - 37 lines] > General network error. Check your network documentation. > Connection Broken My Main dts package calls many other simple dts packages. When my main dts package calls other dts packages getting timeout immediately. Is there any settings at package level to increase the timeout. > can you create a new empty package and create a new connection to the > database? [quoted text clipped - 42 lines] > > General network error. Check your network documentation. > > Connection Broken does your packages are stored in the MSDB database? Have you changed your main package to use the right SQL Server instance? if your main dts package try to open a package on the <old server> and if this server is down, then you'll receive a timeout error. > My Main dts package calls many other simple dts packages. > When my main dts package calls other dts packages getting timeout [quoted text clipped - 52 lines] >> > General network error. Check your network documentation. >> > Connection Broken All packages are refering to the new server only.If I run the package from enterprise manager runs fine no timeout errors. > does your packages are stored in the MSDB database? > Have you changed your main package to use the right SQL Server instance? [quoted text clipped - 58 lines] > >> > General network error. Check your network documentation. > >> > Connection Broken This sounds like a permissions issue. If you can run the packages from Enterprise manager then the issue is not necessarily with the packages themselves. If the packages are executed via a SQL agent job that launches them using dtsrun, then I suggest you check which account the SQL Agent service is running under. It might also have something to do with the owner of the Agent jobs. Packages run via Enterprise Manager run in the security context of the currently logged on user. Packages launched via SQL Agent run in the context of the account that the SQL Agent Service logs in under.  SignatureRegards Darren Gosbell <dgosbell_at_yahoo_dot_com> Blog: http://www.geekswithblogs.net/darrengosbell > All packages are refering to the new server only.If I run the package from > enterprise manager runs fine no timeout errors. [quoted text clipped - 30 lines] > > >> > The new server is very high end server which has 8 processors and > 12GB When running the dts package as a job , it's failing while opening the package itself. Security log in keep filling, is this the cause? This sounds like a permissions issue. If you can run the packages from Enterprise manager then the issue is not necessarily with the packages themselves. If the packages are executed via a SQL agent job that launches them using dtsrun, then I suggest you check which account the SQL Agent service is running under. It might also have something to do with the owner of the Agent jobs. Packages run via Enterprise Manager run in the security context of the currently logged on user. Packages launched via SQL Agent run in the context of the account that the SQL Agent Service logs in under. SignatureRegards Darren Gosbell <dgosbell_at_yahoo_dot_com> Blog: http://www.geekswithblogs.net/darrengosbell In article <eP$UU6osFHA.3164@TK2MSFTNGP14.phx.gbl>, dkrreddy@hotmail.com says... > All packages are refering to the new server only.If I run the package from > enterprise manager runs fine no timeout errors. [quoted text clipped - 30 lines] > > >> > The new server is very high end server which has 8 processors and > 12GB does the SQL Agent service run under a known account? > When running the dts package as a job , it's failing while opening the > package itself. [quoted text clipped - 53 lines] >> > >> > The new server is very high end server which has 8 processors and >> 12GB It sounds like you are running the SQL Agent service under the local system account which does not have access to the DTS packages. The way I normally set up SQL Agent is to run it under a domain user account. This has 2 advantages. 1) It can then access network resources such as file shares (Local System can only access resources on the SQL Server itself) 2) You can also "debug" security issues such as the one you are now having because you can log in under the same account as the SQL Agent and run the packages from Enterprise manager it is often easier to see exactly where your problems are occurring. I would recommend setting up this user with the minimum require security privileges. RESIST the temptation to set this user up as an administrator as it could mean that any user that can schedule a job can run a process under this account. If someone cracks an SA password, they can schedule a job to give them full access to your machine at the OS level and from there possibly out into your network. SignatureRegards Darren Gosbell <dgosbell_at_yahoo_dot_com> Blog: http://www.geekswithblogs.net/darrengosbell > does the SQL Agent service run under a known account? > [quoted text clipped - 26 lines] > > if > >> > this server is down, then you'll receive a timeout error. No, production servers services will always run under domian admin account. This is not the issue. It sounds like you are running the SQL Agent service under the local system account which does not have access to the DTS packages. The way I normally set up SQL Agent is to run it under a domain user account. This has 2 advantages. 1) It can then access network resources such as file shares (Local System can only access resources on the SQL Server itself) 2) You can also "debug" security issues such as the one you are now having because you can log in under the same account as the SQL Agent and run the packages from Enterprise manager it is often easier to see exactly where your problems are occurring. I would recommend setting up this user with the minimum require security privileges. RESIST the temptation to set this user up as an administrator as it could mean that any user that can schedule a job can run a process under this account. If someone cracks an SA password, they can schedule a job to give them full access to your machine at the OS level and from there possibly out into your network. SignatureRegards Darren Gosbell <dgosbell_at_yahoo_dot_com> Blog: http://www.geekswithblogs.net/darrengosbell In article <#bi0pnMtFHA.596@TK2MSFTNGP12.phx.gbl>, willgart@AAAhotmailBBB.com says... > does the SQL Agent service run under a known account? > [quoted text clipped - 26 lines] > > if > >> > this server is down, then you'll receive a timeout error. does the profiler display something? do you see any MSDB access when you try to open/execute a package? have you try to force the server access by creating an alias? you can force the TCP/IP usage or netbios usage; also try an IP address instead-of a netbios or DNS name > No, production servers services will always run under domian admin > account. [quoted text clipped - 52 lines] >> > if >> >> > this server is down, then you'll receive a timeout error. Try logging onto the server under the domain account that the SQL Agent services is running under and try launching the packages interactively from Enterprise Manager. SignatureRegards Darren Gosbell <dgosbell_at_yahoo_dot_com> Blog: http://www.geekswithblogs.net/darrengosbell > does the profiler display something? > do you see any MSDB access when you try to open/execute a package? [quoted text clipped - 29 lines] > > > >> does the SQL Agent service run under a known account? Yes there was msdb access.The following is from sql trace. exec sp_executesql N'exec msdb..sp_log_dtsstep_begin @P1, @P2, @P3', N'@P1 uniqueidentifier,@P2 nvarchar(27),@P3 datetime', '4A1665AF-4C92-457F-B1B1-75B2BCD18E88', N'DTS_Error_Log_DB_DAILY_Step', 'Sep 6 2005 1:42:57:000AM' SELECT N'Testing Connection...' EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters exec sp_executesql N'exec msdb..sp_log_dtsstep_end @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8', N'@P1 bigint,@P2 int,@P3 int,@P4 datetime, @P5 float,@P6 int,@P7 nvarchar(185),@P8 bigint', 48814, 4, 1, 'Sep 6 2005 1:43:13:000AM', 1.634300000000000e+001, -2147467259, N' Step Error Source: Microsoft OLE DB Provider for SQL Server Step Error Description:Timeout expired Step Error code: 80004005 Step Error Help File: Step Error Help Context ID:0 ', 0 exec msdb..sp_get_dtspackage N'DTS_Policy_Driver_SSN', '{C7FF64C3-9006-42C9-BEB7-79775F09A2D2}', null exec msdb..sp_get_dtspackage N'DTS_DM_To_DW_ALL_DAILY', '{06A28751-CA51-410E-9421-907A507A1E04}', null > does the profiler display something? > do you see any MSDB access when you try to open/execute a package? [quoted text clipped - 59 lines] > >> > if > >> >> > this server is down, then you'll receive a timeout error. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.