This morning one of our jobs failed, so I eventually ran the SSIS package manually and found the flow "hanging" at a script task. The fix was to open the script in design mode and hit Save, which I believe compiles the code. Then the package ran as it has normally for several months.
There was a recent Windows update run on this server. I don't know what was updated, as it was the DBA that did that. It seems possible that a .NET framework update would cause this problem, does anyone have any thoughts on this or anything else causing this?
Given the extent of some SSIS environments, and ours is pretty extensive, this could be a real pain to go in and manually recompile each and every script task.
Hi, I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM. However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots)
The same package works fine against a similar test table with 150k rows. http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg
The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table. Any hints,advice would be appreciated.
When I try and create the following stored procedure, I get the following error message: Any ideas as to what went wrong? Here is the stored procedure USE [DBS07]GO/****** Object: StoredProcedure [dbo].[updateMarketName] Script Date: 09/17/2007 22:28:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[updateSubMarketName]( @inMarketId int, @inSubMarketId int, @inSubMarketDescription nvarchar(100), @inActive nvarchar(2), @inLastUpdateDate datetime, @inLastUpdateUser nvarchar(100))AS SET NOCOUNT OFF;UPDATE [SubMarket] SET [SubMarketDescription] = @inSubMarketDescription, [Active]= @inActive, [LastUpdateDate] = @inLastUpdateDate,[LastUpdateUser] = @inLastUpdateDateUserWHERE ([MarketId] = @inMarketId)AND (SubMarketId]= @inSubMarketId) Here is my error message Error message in Red:Msg 137, Level 15, State 2, Procedure updateSubMarketName, Line 12Must declare the scalar variable "@inLastUpdateDateUser".
I have one procedure that gets executed many times per day(thousands at least). I consistently get blocking in my database, as users compile this stored procedure. How can I keep this from recompiling all the time, and bogging down my database? I tried the keep plan option in the portion of the code that uses tembdb, and that doesn't work. thanks.
To all, When a stored proc is executed in SQL server 2000, it is holding a EXCLUSIVE [COMPILE] lock on the proc and the proc os getting recompiled every time it is executed. This is happening with most of the procs that are called from this proc. When multiple users are executing the same process they are having to wait until the other users are done compiling the procs. The lapse time is growing exponentially with multiple users.
I have looked at several places to find a solution for this. Microsoft Articles Q243586 and Q263889 have provided me with some options; but at this point, I need a miracle.
All these procs users temp tables. I have got the code changed to replace most of them with Table datatypes (on SQL2K only) . Some of them still need to use temp tables as they are cross referenced by multiple procs.
I am hopeful, there is some one out there who has dealt with this kind of situations before. Any ideas/sugessions are greatly appreciated..
I have the following table;CREATE TABLE [x_Note] ([x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,[Note] [varchar] (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_x_NoteId] PRIMARY KEY CLUSTERED([x_NoteId],) WITH FILLFACTOR = 90 ON [USERDATA] ,) ON [USERDATA]GOMy clients want me to take the contents of the Note column for each rowand combine them. In other words, they basically want:Note = Note [accumulated from previous rows] + Char(13) [because theywant a carriage return] + Note [from current record].What is the most efficient and relatively painless way to do this? Ithink it might require a cursor, but I'm not sure if there is a moreelegant set-based method to make this happen.
I have a custom application that on occasion requires thousands of TSQLfiles (on the file system) to be compiled to the database.What is the quickest way to accomplish this?We currently have a small vbs script that gets a list of all the files,the loops around a call to "osql". each call to osql opens/closes aconnection to the destination database (currently across the network).
Hello!I am trying to investigate strange problem with particular storedprocedure. It runs OK for several days and suddenly we start gettingand lotof locks. The reason being [COMPILE] lock placed on this procedure. Asaresult, we have 40-50 other connections waiting, then next connectionusingthis procedure has [COMPILE] lock etc. Client is fully qualifyingstoredprocedure by database/owner name and it doesn't start with sp_. I knowthese are the reasons for [COMPILE] lock being placed. Is theresomethingelse that might trigger this lock? When troubleshooting this issue, Inoticed there was no plan for this procedure in syscacheobjects. Thestoredprocedure is very simple (I know it could be rewritten/optimized butourdeveloper wrote it):CREATE PROCEDURE [dbo].[vsp_mail_select]@user_id int,@folder_id int,@is_read bit = 1, --IF 1, pull everything, else just pull unread mail@start_index int = null, --unused for now, we return everything@total_count int = null output, -- count of all mail in specifiedfolder@unread_count int = null output -- count of unread mail in specifiedfolderASSET NOCOUNT ONselect m1.* from mail m1(nolock) where m1.user_id=@user_id andfolder_id=@folder_id and ((@is_read=0 and is_read=0) or (@is_read=1))orderby date_sent descselect @total_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and ((is_read=0 and@is_read=0)or (@is_read=1))select @unread_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and is_read=0GOI was monitoring server for a couple of day before and I am not surewhythis happens every 3-4 days only!Any help on this matter would be greately appreciated!Thanks,Igor
I have a requirement to do a data migration work for a Japanese client. The problem is I don't know Japanese and the client SME does not know English. So, my requirement is, I should be able to create a SSIS package in English and then should be able to compile or translate it into Japanese so that my Japanese SME can understand the package. Is it possible to do? If so, how can I do it?
actually i try to create a native c++ desktop-application that will have to access a SQL CE 3.1 db-file over OLE DB. For that purpose i want to use the same self written wrapper class(for the OLE DB stuff) that i use for a native WM2003 application that creates the db files to be accessed on the desktop application. The WM2003 application including my wrapper class compiles fine with eVC++ 4.0. But now with VS 2005 including the same class i got this compile error: "Redefinition of class IRowsetBookmark". I have to add that i used the application wizard to generate a mfc sdi application with database support(ODBC is used in this app, too). I figured out that in the following 2 inlcuded files IRowsetBookmark is defined: 1)The manually included "ssceoledb30.h" in my wrapper class. 2)The probably from the application wizard included "oledb.h" (in "Microsoft Visual Studio 8VCPlatformSDKInclude")
My workaround for the moment is to comment the class definition in the file "ssceoledb30.h" out because i don't use this OLE DB interface in my wrapper class. But i don't think that this is the way that microsoft supposed to go.
Does anybody had this problem and solved it other way than simply comment one of the class definition out?
I would appreciate it when someone out there knows the answer and could tell me.
I am running the following TSQL command and it hangs. These commands are running from SQL 2000 server and it is querying information for SQL20005 database.
Running simple EXEC command work fine but running using Insert hangs.
Hi Everyone, I'm having a bit of a dispute with one of my clients' software developers. This client has a server, installed by us (Windows 2003 Std. SP1/ SQL 2000 SP3a) (Dual Xeon 2.8, 4GB RAM) They are having performance issues with the software developers application, which is based on SQL. I'm having trouble believing the issues are caused by the server, but the software dev's blaming us for the problems.
Yesterday i got a mail from them that a specific SQL query "hangs" upon execution and this is likely caused by a bad install of the server or SQL (yeah right). This query is: select * from table, wich hangs the server upon running. Theyt say a select row(item) from table does work (duh). Now i know a bit of SQL and i'm thinking this select all from that table query locking up the server is most likely caused by a table that has grown so big that it just locks up.
Am i right thinking this has nothing to do with the hardware/software (sbs/sql), but is merely caused by bad programming and/or lack of SQL knowledge at the dev's side? Especially since last year the DB grew to about 40 GB and i had to tell them to think about putting it in simple mode to make it smaller. Something they'd never heared about...
Any ideas would be very welcome. I don't want to go throw mud around with us blaming them and them blaming us and the client being the one with the problems. I just want to make a well thought out statement to the software dev. and then try to find the right solution.
I had 5 servers hang all on the same day, all within about 5 mins of each other scattered across 4 corners of the UK. Our support team tell me that it has something to do with SQL Server, but cannot say what. I can see looking at the error log of one of the servers, that there are about4 entries everyday since startup, and this continues right up until the night before the freeze.
At 22:00 the night before the freeze, a load of entries are written (Iv'e cut down the list to make it easier to read). then at around 4am the servers all froze. Could someone take a sneaky peak at these logs and tell me in your own opinion what caused this to happen.
The first section, is what I believe to be normal
2006-11-28 20:52:53.68 server Copyright (C) 1988-2002 Microsoft Corporation. 2006-11-28 20:52:53.68 server All rights reserved. 2006-11-28 20:52:53.68 server Server Process ID is 952. 2006-11-28 20:52:53.68 server Logging SQL Server messages in file 'D:Program FilesMicrosoft SQL ServerMSSQLlogERRORLOG'. 2006-11-28 20:52:53.73 server SQL Server is starting at priority class 'normal'(1 CPU detected). 2006-11-28 20:52:54.75 server Working Set size set to 409600 kilobytes. 2006-11-28 20:52:54.90 server SQL Server configured for thread mode processing. 2006-11-28 20:52:54.90 server Processor affinity turned ON, processor mask 0x1 2006-11-28 20:52:54.92 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2006-11-28 20:52:55.20 server Attempting to initialize Distributed Transaction Coordinator. 2006-11-28 20:52:57.20 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b 2006-11-28 20:52:57.53 spid3 Starting up database 'master'. 2006-11-28 20:52:59.31 spid3 Server name is 'SN047483'. 2006-11-28 20:52:59.31 server Using 'SSNETLIB.DLL' version '8.0.760'. 2006-11-28 20:52:59.31 spid5 Starting up database 'model'. 2006-11-28 20:52:59.50 spid8 Starting up database 'msdb'. 2006-11-28 20:52:59.50 spid9 Starting up database 'pubs'. 2006-11-28 20:52:59.53 spid10 Starting up database 'udb'. 2006-11-28 20:52:59.64 spid11 Starting up database 'vgold'. 2006-11-28 20:53:00.07 server SQL server listening on 155.202.58.53: 1433. 2006-11-28 20:53:00.07 server SQL server listening on 127.0.0.1: 1433. 2006-11-28 20:53:00.29 spid5 Clearing tempdb database. 2006-11-28 20:53:00.64 server SQL server listening on TCP, Shared Memory, Named Pipes. 2006-11-28 20:53:00.64 server SQL Server is ready for client connections 2006-11-28 20:53:00.71 spid9 Starting up database 'dbs'. 2006-11-28 20:53:01.01 spid8 Recovery is checkpointing database 'msdb' (4) 2006-11-28 20:53:01.50 spid5 Starting up database 'tempdb'. 2006-11-28 20:53:53.78 spid3 Recovery complete. 2006-11-28 20:53:53.78 spid3 SQL global counter collection task is created. 2006-11-28 20:53:54.32 spid59 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'. 2006-11-29 00:00:01.10 spid69 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_instance_regread'. 2006-11-29 02:00:01.54 spid69 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'. 2006-11-29 02:00:09.31 backup Database backed up: Database: master, creation date(time): 2006/11/28(20:52:57), pages dumped: 1907, first LSN: 159:245:1, last LSN: 159:247:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-11-29 02:00:11.46 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 1667, first LSN: 397:332:1, last LSN: 397:335:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-11-29 02:01:58.10 backup Database backed up: Database: dbs, creation date(time): 2004/10/05(08:00:04), pages dumped: 94467, first LSN: 114:53199:1, last LSN: 114:53203:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-11-29 02:01:58.20 backup Database log truncated: Database: dbs. 2006-11-29 10:51:33.43 spid62 Using 'odsole70.dll' version '2000.80.760' to execute extended stored procedure 'sp_OACreate'. 2006-11-30 02:00:04.34 backup Database backed up: Database: master, creation date(time): 2006/11/28(20:52:57), pages dumped: 1907, first LSN: 159:260:1, last LSN: 159:262:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-11-30 02:00:06.57 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 1667, first LSN: 398:26:1, last LSN: 398:29:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-11-30 02:01:53.84 backup Database backed up: Database: dbs, creation date(time): 2004/10/05(08:00:04), pages dumped: 94467, first LSN: 114:54895:1, last LSN: 114:54899:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-11-30 02:01:53.93 backup Database log truncated: Database: dbs. 2006-11-30 13:10:09.96 spid67 Error: 60002, Severity: 15, State: 1 2006-11-30 13:10:09.96 spid67 PBX login_id 5592 already exists in user id 379. Proc Name: um_user_insert. 2006-12-01 02:00:04.51 backup Database backed up: Database: master, creation date(time): 2006/11/28(20:52:57), pages dumped: 1907, first LSN: 159:275:1, last LSN: 159:277:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-12-01 02:00:06.76 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 1667, first LSN: 398:408:1, last LSN: 398:411:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-12-01 02:01:52.31 backup Database backed up: Database: dbs, creation date(time): 2004/10/05(08:00:04), pages dumped: 94515, first LSN: 114:55915:1, last LSN: 114:55923:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'QMBACKDEVICE'}). 2006-12-01 02:01:52.39 backup Database log truncated: Database: dbs.
This second section then has around a good few hundred entries.
2006-12-01 22:00:01.98 spid67 Error: 60132, Severity: 15, State: 1 2006-12-01 22:00:01.98 spid67 Folder 7 is not assigned to user 11 Proc Name: ags_workflow_sess_count. 2006-12-01 22:00:02.04 spid67 Error: 60132, Severity: 15, State: 1 2006-12-01 22:00:02.04 spid67 Folder 7 is not assigned to user 11 Proc Name: ags_workflow_sess_count. 2006-12-01 22:18:02.57 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 22:18:02.57 spid66 Folder 7 is not assigned to user 63 Proc Name: ags_workflow_sess_count. 2006-12-01 22:43:37.42 spid67 Error: 60132, Severity: 15, State: 1 2006-12-01 22:43:37.42 spid67 Folder 7 is not assigned to user 493 Proc Name: ags_workflow_sess_count. 2006-12-01 22:43:37.43 spid67 Error: 60132, Severity: 15, State: 1 2006-12-01 22:43:37.43 spid67 Folder 7 is not assigned to user 493 Proc Name: ags_workflow_sess_count. 2006-12-01 22:44:37.51 spid58 Error: 60132, Severity: 15, State: 1 2006-12-01 22:44:37.51 spid58 Folder 7 is not assigned to user 613 Proc Name: ags_workflow_sess_count. 2006-12-01 22:46:07.00 spid67 Error: 60132, Severity: 15, State: 1 2006-12-01 22:46:07.00 spid67 Folder 7 is not assigned to user 660 Proc Name: ags_workflow_sess_count. 2006-12-01 23:00:20.65 spid58 Error: 60132, Severity: 15, State: 1 2006-12-01 23:00:20.65 spid58 Folder 7 is not assigned to user 949 Proc Name: ags_workflow_sess_count. 2006-12-01 23:00:20.67 spid58 Error: 60132, Severity: 15, State: 1 2006-12-01 23:00:20.67 spid58 Folder 7 is not assigned to user 973 Proc Name: ags_workflow_sess_count. 2006-12-01 23:02:33.07 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:02:33.07 spid66 Folder 7 is not assigned to user 1010 Proc Name: ags_workflow_sess_count. 2006-12-01 23:04:38.10 spid58 Error: 60132, Severity: 15, State: 1 2006-12-01 23:04:38.10 spid58 Folder 7 is not assigned to user 1247 Proc Name: ags_workflow_sess_count. 2006-12-01 23:04:38.12 spid58 Error: 60132, Severity: 15, State: 1 2006-12-01 23:04:38.12 spid58 Folder 7 is not assigned to user 1266 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.59 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.59 spid66 Folder 7 is not assigned to user 1659 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.59 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.59 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.59 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.59 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.60 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.60 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.60 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.60 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.62 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.62 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.62 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.62 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.62 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.62 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.62 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.62 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.64 spid66 Error: 60132, Severity: 15, State: 1 2006-12-01 23:06:08.64 spid66 Folder 7 is not assigned to user 1812 Proc Name: ags_workflow_sess_count. 2006-12-01 23:06:08.64 spid66 Error: 60132, Severity: 15, State: 1
This section shows from 4am onwards
2007-01-13 04:05:09.87 spid71 Error: 60132, Severity: 15, State: 1 2007-01-13 04:05:09.87 spid71 Folder 7 is not assigned to user 613 Proc Name: ags_workflow_sess_count. 2007-01-13 04:05:13.40 spid75 Error: 60132, Severity: 15, State: 1 2007-01-13 04:05:13.40 spid75 Folder 7 is not assigned to user 660 Proc Name: ags_workflow_sess_count. 2007-01-13 04:05:16.15 spid77 Error: 60132, Severity: 15, State: 1 2007-01-13 04:05:16.15 spid77 Folder 7 is not assigned to user 660 Proc Name: ags_workflow_sess_count. 2007-01-13 04:05:17.29 spid61 Error: 60132, Severity: 15, State: 1 2007-01-13 04:05:17.29 spid61 Folder 7 is not assigned to user 660 Proc Name: ags_workflow_sess_count. 2007-01-13 04:05:18.12 spid69 Error: 60132, Severity: 15, State: 1 2007-01-13 04:05:18.12 spid69 Folder 7 is not assigned to user 63 Proc Name: ags_workflow_sess_count. 2007-01-13 04:10:28.81 spid51 Unable to load OLE/DB initialization service. 2007-01-13 04:58:12.21 server Error: 17883, Severity: 1, State: 0 2007-01-13 04:58:12.21 server The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x250A5C68. 2007-01-13 04:59:12.21 server Error: 17883, Severity: 1, State: 0 2007-01-13 04:59:12.21 server The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x250A5C68. 2007-01-13 05:00:12.21 server Error: 17883, Severity: 1, State: 0 2007-01-13 05:00:12.21 server The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x250A5C68. 2007-01-13 05:01:12.21 server Error: 17883, Severity: 1, State: 0 2007-01-13 05:01:12.21 server The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x250A5C68. 2007-01-13 05:02:12.21 server Error: 17883, Severity: 1, State: 0 2007-01-13 05:02:12.21 server The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x250A5C68. 2007-01-13 05:03:12.21 server Error: 17883, Severity: 1, State: 0
Hi Guru,Our HR sql 2000 production server is 800.760 built (SP3a) with 2GB ofRAM and 2 CPU hiper threading. This server is very low activity whereprocessor runs average 1-3 during the day. However, in the last twomonths, I've seen the following message occurred a few times on thisserver.Error: 17883, Severity: 1, State: 0The Scheduler 2 appears to be hung. SPID 0, ECID 0, UMS Context0x0381F660.I did extensive research and read both KB 815056 and 10885 but I couldnot correlate these KBs in to my issue. The reason is, I don't thinkour server is very busy to cause the checkpoint process to be delayed.The database keeps growing only 5-10MB in a week and I allocated thespace during off hour.What is the solution to fix this issue? Will I fix this issue byapplying the hotfix MS03-031?Thanks so much,Silaphet,
hi,everyone!I meet strange thing when I create a maintance job for backup alldatabase.and this backup job just stands "Executing job step '(step 1)'" for averylong time .SQL server startup account is this account for network bakcupafter I issue sp_databases sp, I found my full database size only 1GB,and I found my backupfile is complete.I view event log and Sql server Log and no found error!why do it take so long time, but stands 'executing ' status and neverfinish.help me!best regards!
My SQL CE Replication is hanging during a full CreateDatabase download. My SDF file is around 13 MB loaded onto an SDIO card. I need to be able to blow away the SDF file and fully reload from remote locations.
I get all of the table 'upload' and 'download' async events and it appears to be working but then just hangs until I guess the device runs out of memory. I get several beeps and then the hourglass. After a reboot, the DB file is not usable.
I've been using SQL CE 3.0 for awhile and it has worked ok in the past. I upgraded to SQL CE 3.5 but I see the same behavior.
My replication data consists of about 30K inserts across about 30 tables.
Any ideas on how to debug a problem like this? I'm really stuck with this bug.
I have a job that FTP's a backup file offsite daily. Sometime the job hangs (for undetermined reason). The job never terminates. Is there ar way to force termination of the job after so many minutes? This is a Operating System command job.
I see the parameter "Shutdown Timeout Interval" in SQL Server Agent - currently set to 15 seconds. This apparently is not used or not working, as my job was hung for several days and it was not detected. The job runs daly. Since the prior run was "still running - hung", it didn't start, causing my offsite backups to fail following the job hang.
How can I force termination of a job if it doesn't complete in so many minutes?
The stored procedure, below, results in this error when I try to compile...
Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'.
However the select statement itself runs perfectly well as a query, no errors.
The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.
What gives with this?
Thanks in advance R.
The code:
Code Snippet
-- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80);
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! );
Hi, excuse me if this problem has been discussed before!
We handle the addmittance in our university with IIS 4.0 and MS SQL Server 6.5 ... a group of 12 operators keep the server busy entering the information about students... I run backup of the hole database on every 1 hour in order to have a recent copy of the date base in case the disk fail (we had bad luck with a quantum HDD few weeks ago). The problems begun when the data base got to 18 MEGs and at about 32 connections to the SQL server when the dump finish the server goes out...
Anyone heard about this before and how to handle it? Yours truly WarJo
hello: My situation is (1) I upgraded MS SQL 7.0 from original edition to SP 4 (2) DTS packages which download data from an oracle server work fine both on a remote machine and on the Server (3) When these packages are scheduled to run as jobs they do not succeed and do not fail they just keep running e.g for 58 hours over the weekend (4) even if I start the job on the server I still get the same behaviour:
We have initiated reinitialize subscription by which snapshot agent is started and hanged at 26%.We have a publication of 6 articles, transactional replication, using SQL Server 2008R2.
Hi guys, I am having a serious problem on my SQL server. Assuming there's a web server (Cold Fusion in IIS) connects to my SQL server (SQL server 2005 and million records in it which consider quite big for the db size), it works fine and run smoothly during weekday. However, there will be lots of users accessing the db through my web application (located at my web server) until my web application get hang. Not only that, even I want to access my SQL server from my place using management studio also fail.
I try remote desktop into the SQL server, view the 'Task Manager', it shows 1-2% CPU usage that it takes; and i'm able to open the SQL server using management studio. Therefore, I try to restart my web service( IIS ) , and it still unable to open my web application. However, once I restart the SQL service, my web application is available. This issue happens to me every weekend with several times. It's no point for me to restart the SQL service again and again.
1) What's the reason that I need to restart the SQL service then only I can open the web application? Is it the SQL server hang or the connection problem?
2) Is it lots of record in the database causing the server gets hang since lots of user accessing the database? 3) Where can I check out the exactly problem on it ? IIS log or SQL log?
Hope can get any assistance from here and get a solution/suggestion from you guys as soon as possible.
I have a problem with a DTS package to import data from one of our Ingres based Databases.
I've recently moved the DTS package from one server to another and recreated the Ingres ODBC Settings, which are all OK.
When I run the package through EM and the CMD line, it runs OK, but not as a scheduled task.
I've had a look at a KB Article Q318819 and followed the steps about package threads on 3rd party drivers, but this doesent help, nor does making sure DTSRun isnt in the current task list.
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items> from A left join B on B.id_A = A.id left join C on C.id_B = B.idthis now looks like select <many items> from A left join (B join C on C.id_B = B.id ) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
Hi.. We have a java application which connect to our MS-SQL DB during midnight using JDBC connector and extract information. But very often ,the java applicatian halted and because of the deadlock as shown below.
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Transaction (Process ID 54) was deadlocked o n lock resources with another process and has been chosen as the deadlock victim.
now the java programmer is blaming me that the MS SQL DB is having deadlock and cause their Java application to hang.
Is there any way to clear the deadlock before the the Java applcation run or how to prove the Java application script itself is having problem and causing the deadlock ?
