Dear Group,
I am tring to use a command that calls the server to fill an adapter, it
never seems to get to the adapter, command and the server either times out
or does not respond. The timeout is set at 10 hours. I am using Visual
Studio to acces MS SQL - Server.
I think I have all the rights and permissions set correctly. Also, I have
used this command to fill other adapters and tables.
Does anyone have a suggestion.
Jeff Magouirk
Hello,
Has anyone experienced timeouts on SQL Server 2000 that happen at regular intervals?
Regards,
Brent.
Anyone else expirencing some heavy duty timeouts?
View 2 Replies View Relatedof query timeouts in SQL Server Management Studio?
I have increased the timeout setting in many places and still receive a timeout message within 40 seconds for certain update queries that involve large tables and many records. The only workaround is to break up the job into smaller queries but this makes management, unmanageable.
Is there a solid alternative to SQL Server Management Studio?
hi!I am having some timeout issues.I am running sql 2k with 3gig available ram.I did a 600,000 record delete on a table that gets written to by theactive/production application and my application timed out when it wasdoing the delete.why did stored procedures that ran fine before I started the largedelete slow down?Some of the procedures which slowed down were accessing the same tablewhere i was doing the delete.Thanking you in advance!!parez
View 1 Replies View RelatedI am getting Timeout Errors quite often and cannot figure out why. I am using Enterprise Library 2.0 when accessing the database. It is not from any particular function or page either and when I check the database there may only be 2 or 3 connection from my app. Any ideas of what could be causing this? Below is my error and stack.
Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Stack:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at HM.Security.SecurityData.GetAllUsers(Int32 filter) at HM.Admin.Security.SecuritySearch.SetUpAutoFill() in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 74 at HM.Admin.Security.SecuritySearch.Page_Load(Object sender, EventArgs e) in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 26 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Hello all,
I am managaing an NT infrastructure that supports a web application. The web server is configured to use an ODBC System DSN to access data on an SQL server over a 100MB private segment. When users run a particular query through the web page, they get an ODBC "Timeout Expired" message.
Does anyone know a fix for this. I could not find anything that explicitly identified this problem in MS Support. The query runs fine from an Eneteprise Manager T-SQL window both on the server and remotely.
Thanks in advance,
Ed Molinari
Technical Architect
Eamerald Solutions
A timeout issue is occurring resulting in following error.
This issue is sporadic and may be related to network.
This error occurs on the client: Microsoft OLE DB Provider for SQL Server error `80040e14`
OLE/DB provider returned message: Timeout expired
"conn_info".asp, line 444
Subject: SQL server job timeouts?We have a job that uses WinHTTP inside a stored procedure. We haveanother SP wrapper that runs this for a couple hundred records.When we add this as a job using SQLAgent it times out half way. Runningit from SQL Query analyser and it completes to the end.Anyway to set the timeouts for jobs?
View 5 Replies View RelatedI've had my SQL server database running for two years now without aproblem.However, just today one of the main tables started returning an error.The table is contained within a database called engineering. I backit up once a week and the file size is up to about 40 MB.The error returned when trying to return data from one table(DbLucent) is:"[Microsoft][ODBC SQL Server Driver]Timeout expired"I can open/query any of the other tables in the database. I can opendesign table for this table. But it won't return any query.I'm debating whether to restore the database from the last backup.Any suggestions would be appreciated. Being located reomotely, Irather not fly back to the city where the server is and work on itthere either.-David
View 6 Replies View RelatedI'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.
I'm using the following db connection string:
Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;
As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.
The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
After monitoring and debugging, I've found two things.
1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.
2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?
I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)
Any help is deeply appreciated!
I have a basic UPDATE that's something along the lines of:
UPDATE LocalTable SET LocalColumn = RemoteColumn
FROM LocalTable INNER JOIN server.catalog.dbo.RemoteTable ON (...)
After running in Query Analyzer for ten minutes, I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].
I checked the linked server settings in EnterpriseManager and both timeout settings are at 0 (no timeout). Any idea how I can remove whatever timeout is causing this?
Anyone know how remote join performance compares to moving remote data over via DTS, running an UPDATE, and dropping the data?
I'm having a problem with SQL Server 2005 not being able to send all of my messages. Occasionally when we batch a large number of messages together, we run into problems because the SMTP server can't process them fast enough, and the message doesn't send due to a timeout. I know I have increase the number of retries, but I'd rather just increase the timeout setting. Does anyone know where I might be able to do this at? The mail admins are also looking at modifying the rules in SMTP so that messages from these servers don't go through the vigor of SPAM and phishing detection. Hopefully that will speed it up, but I'd like to give it a little extra leniency on the SQL side as well. Any suggestions?
View 3 Replies View RelatedI have two problems I need some help with.First, I've just inherited a system and am delving into a few timeoutproblems that are causing problems for the users.Now, if I do a simple select * from the table (which looks to be thecause of the problem at this stage) in QA, I get the results back inless than a second. If I open the table in EM it takes about 10. Isthere a difference in viewing the data this way ? I'm used to EM beingvirtually the same speed. There is only one row. Minor questionreally, just something I'd like to understand if there is adifference.CREATE TABLE [QUERY] ([QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[CAT_ID] [numeric](18, 0) NOT NULL ,[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_ID] [int] NOT NULL ,[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULLCONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT(0),[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT(0),[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED([QUERY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY([CAT_ID]) REFERENCES [QUERY_CATEGORY] ([CAT_ID]) ON DELETE CASCADE ON UPDATE CASCADE) ON [PRIMARY]GOI don't think any re-indexing has been done on this (or the othertables in the db). I was wondering if constant adding/deleting rowscould cause the index to be massive and in need of a good clear out.Any pointers would be appreciated. From what I can tell, there wassome problems trying to get replication to work. I need to dig deeperto see if this is now correct.-------------------------Secondly, there is a another table in the same database.CREATE TABLE [FIELD_DATA] ([ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DATA_ID] [numeric](18, 0) NOT NULL ,[FIELD_ID] [numeric](18, 0) NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOIt holds approx 4 million rows. The rest of the tables have minimaldata and about the same amount (consider them the same if you will).Now, another 'copy' of this database is held elsewhere (differentclient data) and this holds 40 million rows. The difference is thatthe first DB is 4.5GB and the second 6.5GB (approx). Does this provemy theory that re-indexing would be a good idea ?ThanksRyan
View 3 Replies View RelatedHiGot a strange problem.For some reason our web client box times out occasionally. Maybe afew times a day.Nothing appears in the logs.What I do know is that the disk queue ramps up to <high>(think top ofthe graph), processor queue jumps up and the tps drops to 0 (naturallyenough!) along with reduced pagefile usage. Usually at this time thetps is between 20 and 300. Running a pair of mirrored 18gb scsi disksfor the whole server (yeah yeah) and a whopping 640mb memory. Oh anda single piii 1 gig. Sql2k standard vanilla, server2k.What server trace events is it worth my catching to try and get abetter Idea of what may be causing this? No major jobs (backups etcseem to be occuring at these times).Cheers
View 1 Replies View Relatedim testing some database cleanup scripts that dont seem to be working. im calling stored procedures through a VBScript file to clean out a table in the database at night. there are a couple of tables to clean out (around 12 tables) and i have a sleep in between each call to the stored procedure.
the sqlcmd im using is in this format:
cmd /C osql -E -S computernameSQLEXPRESS -d database -Q ""{CALL SP_MY_PROC}" -l 20 -o debug.txt
so this is called a few times, once for each table, and sleeps for ~10 seconds before the next call to give some lead time.
the output i see is below
[SQL Native Client]SQL Network Interfaces: Error Locating
Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.
now when i run these SPs manually in SSMSE, it executes fine with no problems. is there some timeout valuee im doing wrong? or am i using the wrong syntax for the osql?
Getting an issue with SQL Server 2005 in my production environment and
wanted to see if anyone else has seen similar behavior. First the
environment:
SQL Server 2005 on the backend, main site is running on ColdFusion 5
and there are various internal applications written in VB6 and .NET.
Upgraded to SQL Server 2005 about three weeks ago and since then about
once a week the server throws a fit and causes all sorts of timeouts.
Event Viewer is showing a bunch of same event:
Event ID: 8628
Description: A time out occurred while waiting to optimize the query.
Rerun the query.
The events occur a rate of about 10/minute and basically lock up the
server. Last time this happened I was not able to open a connection to
the SQL Server to even analyze the issue. All connections timed out...
Had to shutdown and restart the server to recover.
The first time this happened I saw a lot of waittypes
RESOURCE_SEMAPHORE_QUERY_COMPILE in the processes table and thought
that this might be related to the known bug with 2005 that has to do
with implicit conversions between Ascii and Unicode. This might be the
case since I know we have a lot of NVARCHAR columns and most searches
are using ASCII text so the result looks something like this:
SELECT cols FROM table WHERE nvarchar_column = 'Something'
There is a published article regarding this issue here:
http://support.microsoft.com/?kbid=919636
Any thoughts?
Thanks
I'm having an issue with what appears to be SQL Server 2005 deciding to randomly ignore new connections.
I currently have two virtual servers - one running just SQL Server 2005, the other running Reporting Services, Windows Sharepoint Services and Team Foundation Server.
For 3 weeks, it was all working perfectly, then on Wednesday night the server (and both Virtual Servers) was rebooted after installing the latest updates for Windows. Since then, I've had this issue.
It will work fine for a while, then it'll start throwing loads of Errors and Warnings into the Event Log, all along the lines of unable to connect to the database. The Reporting Services Configuration utility throws up the same problem. Then randomly, it'll start working again.
If anyone has any ideas, they would be much appreciated as this is driving me crazy!
Thanks!
We get the following errors at the same times every night (between about 3AM and 4AM) on our distributor (ss2000). The publisher and subscribers do not log anything unusual during this time period but are busy. The distribution agents time-out with:
Timeout expired (Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
---------------------------------------------------------------------------------------------------------------
exec sp_MSadd_repl_commands27hp 2, N'pacefs', 0x0013D1040000A2BF00010013D119000008080142000003D70002F4A10000001E0003A1000000003E01000029007B00430041004C004C002000730070005F004D00530069006E0073005F00740062006C005F0063006F006E00760065007200730069006F006E005F006400610074006100200028003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F0029007D00060000004C007B00370030004300460037004100420041002D0041003600430034002D0034003300340045002D0039003500310045002D003300360037003000410044003500320036004500330035007D000C00050000005765656B310C00000C00000043757272656E744D6F6E746801000000010000000C000000060000003173742054440100010000000C00000008000000484C4320576573740C00000009000000526566696E616E6365010000480000000C000000050000004E61696C730C000004000000323239340C000000090000004A656464204C6172610C00000C0000004C656E64696E6720547265650C000000000000000C00000007000000333431353338320C0F0000003239353433323330202020202020200E00002E0032003000300037002D00300039002D00310030002000310032003A00350036003A00330034002E003400360030000C000B0000004C454E44494E47545245450C130000004C454E44494E4754524545204E4554574F524B0C09000000524546494E414E43450100000000000001000000000000000713040016003400300032003000300030002E0030003000300030000713040016003400300035003000300030002E00300030003000300001000000540000008E01000000000000010000000000000001000000000000000C000000090000004A656464204C6172610C0000050000004E61696C730C000008000000484C4320576573740C000000060000003734363937330C0002000000464C0C0000000000010000002005000001000000480000000100000001000000010000000000000001000000000000008E, 0x0013D1040000A2BF00010013D119000008080142000003D70002F4A20000001E00033D000000003E01000029007B00430041004C004C002000730070005F004D00530069006E0073005F00740062006C005F0063006F006E00760065007200730069006F006E005F006400610074006100200028003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F0029007D00060000004C007B00370031003500370037004500420031002D0034003200450035002D0034004300420041002D0039004600320036002D003300360037003000420030003400450039004200370030007D000C00000000000C000000070000004D6F6E74683131810C0000000A000000556E61737369676E6564818C0C00000006000000457175697479810C040000004E6F6E650C000000030000003931360C0C0000004E69636F6C61205365
exec sp_MSadd_distribution_history 7, 3, N'The process is running and is waiting for a response from one of the backend connections.', 0x0000000000000000000000000000, 0, 0, 0.00, 0x00, 1, 0x0000000000000000000000000000, 0, 0x01, 0x01
This is driving me crazy as there appears to be little to go on and I don't know what to do next.
Any useful suggestions?
Thanks,
Michael
I was given a list of user comments from an old application and told to match it up with our current system. After all the easy methods I was still left with 95% of the records unmatched. The only way I had left was to try and match up the names. Given that people could have the same name I added a count to make sure it only found one record. This query has been running for over an hour now. How do I tell if it's actually running, or if it locked up?
Also here is the query I'm using, does anyone have any feedback on it? Is this poorly written, is my logic wrong? Did I put the server in a loop?
UPDATE user_table_1SET id = s.idFROM user_table_2 AS sWHERE RTRIM(s.first_name) = RTRIM(user_table_1.first_name) AND RTRIM(s.last_name) = RTRIM(user_table_1.last_name) AND (SELECT COUNT (*) FROM user_table_2 AS cs WHERE RTRIM(user_table_1.first_name) = RTRIM(cs.first_name) AND RTRIM(user_table_1.last_name) = RTRIM(cs.last_name)) = 1
I have a database that I am using as an archive for emails and am storing them in varbinary(max) data types. The database works fine for inserts and retrieval but I cannot delete large sets of records ( > 30K) without it timing out. I assume this is because SQL server isn't simply releasing the handle to the blob but is instead doing a lot of work reclaiming the space.
Is there any flag I can set or approach I can use to resolve this issue?
I was considering moving the blobs into a seperate simple table and putting async triggers on the primary to delete the blobs, will this work?
Any ideas are appreciated. Besides the "store files in the file system" idea.
- Christopher.
Hi,
A coworker of mine is experiencing some problems with SQL Server Integration Services (SSIS) and long running Web Service calls. Any feedback on the problem would be greatly appreciated.
The problem is as follow:
He has a large SSIS-package that, among other things, contain a few Web Service Tasks. All of the tasks are executing as expected for small amounts of data. All the Web Service Methods have in common that they have a long running time. The running time depends on the amount of data.
During requests with large amount of data the Web Service task fails with the error message €œThe operation has times out€?, but on the server they see that the service call completes as expected (after quite some time, approx 200sec).
The Web Service tasks are using an €œHTTP Connection Manager€?, created in the SSIS package. The timeout-value is set to 300 sec (which is the largest value for the property). My coworker expected that this value was the same as setting the timeout value for a Web Service proxy object for any other project.
After testing the Web Service from a Console application, with 300 sec timeout, the app executed as expected with no timeout exception.
After doing some testing, he€™s quite sure that the SSIS task times out after 100 seconds, which is the default .NET 2.0 timeout value for a WS proxy, even though the timeout property in SSIS is set to 300 seconds.
As a work around he have created a console application that does the web service call, and then use the €œexecute process task€? in SSIS. This is off course a €œdirty€? hack, and he want€™s to use the €œHTTP Connection Manager€? task instead.
So the question is: Have anyone experienced this problem before? Is there a limitation on the HTTP Connection Manager task? Is the timeout value on the HTTP Connection Manager task the same as setting the timeout property on a WS proxy? Why isn€™t this €œtimeout€? value picked up my SSIS?
Again, thanks for any help!
Hello all,
Has anyone else experienced random SQL timeouts and checked the processor usage and found that processors 0 and 1's usage patterns are mirrored (when one goes up the other goes down)? We're running into this all of a sudden.
SQL 2000
fairly small db and very low usage at this time < 100 connections/hour.
only web services, SQL 2000 running on the machine, SQL 2005 is installed but currently stopped.
1GB memory
Windows Server 2003 SP 1.
2 processors (dual core) in use.
SQL Usage of the processors according to performance monitor becomes an exact mirror where proc 0 goes high proc 1 goes low, if proc 1 is 25% then proc 0 is 75%.
Thanks
Bob
we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.
This settings should be sql server 2008 related.
Hi,
I'm new to this SQL thing, and I inherited a DB that has a job that runs every two hours 24/7. Normally the process takes about 5 minutes to complete, but on occasion it just doesn't finish. In those cases, it cannot run at the next two hour cycle. If this happens on a Friday evening, it doesn't get noticed until Monday morning. That is a Bad Thing (tm).
Someone told me to create a second job that would run 45 minutes behind the first and automatically stop the first job if it hadn't terminated on it's own. My problem is I cannot find anywhere how to do this. I have found how to start other jobs, but not stop them.
Thanks in advance!
- Adam
It appears that our web server (IIS 4) stopped responding after running
some BCP jobs. Anyone know why BCP would interfere with IIS.
SQL and IIS are on the same server. Anyone had problems with IIS/SQL getting along?
After the problem showed up, SQL responds fine in ISQLw, but the Web service goes out to lunch.
I scheduled jobs to take backups, but i found out that sqlserver agent is stopping by itself, what could be the solution for this problem.
Thanks in advance
I created some test data in two tables. Then I went to one of the tables and right clicked then Selected Script Table asSelect ToNew Query Editor Window. I then cleared the generated data and selectedDesign Query in Editor. I then picked both tables in the 'Add Table' window and picked 'Add'. This produced two windows and I made the links I needed and clicked OK. This generated the SQL I wanted and it works great! But when I close the SQL Express and then re-open it the SQL does not work! I open SQL Express and selectFileOpenFile and pick the SQL I had just saved. Then I pick Execute I get the following message.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.FileData'
How can I save SQL that works and then not be able to use it?
Beats Me,
Miname
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I have had my site running for several months now. My pages retrieve data from the SQL Server on the same machine. Today my users are are getting the message "SQL Server does not exist or access denied"
I have made sure that the credentials are correct. I can use those credentials to create a DSN on the server. So I don't know where to look for the problem. Help please!
I have created a stored procedure in SQL Server. I found it very slow, so i putted "select getDate(), 'testposition 1'" at different places, so I could see what part of the code that takes time.
The problem is: Depending on where I put the select statements, the execution of the stored procedure seems to just stop. And depending on where i put the select statements, it stops at different places.
This is how I do (example):
1. I re-create the stored procedure with some "select getDate()"-statements
2. I run the stored procedure 15:00:00
3. I cancel the stored procedure after 20 seconds and look at the resultsets. All getDate-functions show a time between 15:00:00 and 15:00:02
4. I run the stored procedure 15:01:00
5. I cancel the stored procedure after 5 seconds and look at the resultsets. The same amount of resultsets are showed, so I can make the conclusion that the execution stopped at the same place as last time. All getDate-functions show a time between 15:01:00 and 15:01:02 this time too.
6. I re-create the stored procedure with some new "select getDate()"-statements
7. Now the execution stops at an other position. Somtimes even between two "select getDate()"-statements!
I pasted the whole stored procedure here:
drop PROCEDURE spUpdateASW
go
create PROCEDURE spUpdateASW
AS
DECLARE @DataBatchID int
DECLARE @DataHeaderID int
DECLARE @ASWTableID int
DECLARE @ASWTableName varchar(25)
DECLARE @ASWFieldName varchar(25)
DECLARE @AllowASWUpdate tinyint
DECLARE @IsPrimaryKey tinyint
DECLARE @DataTypeIsNumeric tinyint
DECLARE @Data varchar(100)
DECLARE @SQL_Where as varchar(400)
DECLARE @SQL_Insert as varchar(1000)
DECLARE @SQL_InsertValues as varchar(400)
DECLARE @SQL_Update as varchar(1000)
DECLARE @updateCounter int
DECLARE @whereCounter int
DECLARE @SQL_CheckIfAlreadyExist as varchar(1000)
DECLARE @ErrorMessage varchar(500)
DECLARE @RuleWhen as varchar(50)
DECLARE @RuleWhenToExec as varchar(500)
DECLARE @tempStr as varchar(700)
DECLARE @server varchar(50)
DECLARE @shortServer varchar(50)
SET @server = 'GIBSON_A3MFGF_T1.S44E5797.A3MFGFT1'
SET @shortServer = 'GIBSON_A3MFGF_T1'
DECLARE @SQL varchar(5000)
select getdate(), 'testposition 1'
CREATE Table #tmptblUpdateASW(
ASWRowAlreadyExists int,
RuleWhenIsValid int
)
INSERT INTO #tmptblUpdateASW(ASWRowAlreadyExists, RuleWhenIsValid) Values(-1, -1)
DECLARE Batch_Cursor CURSOR LOCAL FOR
SELECT DataBatchID from tblDataBatch
where DateConverted is not null and ASWUpdateStarted = 0
and DataBatchID not IN(
select fkDataBatchID from tblDataHeader where DataHeaderID IN(
select fkDataHeaderID from tblASWData where ConversionErrorMessage is not null
)
)
OPEN Batch_Cursor
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
WHILE @@FETCH_STATUS = 0
BEGIN
Update tblDataBatch set ASWUpdateStarted = 1 where DataBatchID = @DataBatchID
DECLARE Header_Cursor CURSOR LOCAL FOR
SELECT DataHeaderID
from tblDataHeader
inner join tblAgileFieldType on tblDataHeader.fkAgileFieldTypeID = tblAgileFieldType.AgileFieldTypeID
where fkDataBatchID = @DataBatchID and isSentToASW = 0 order by tblAgileFieldType.InsertOrder
OPEN Header_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ASWTable_Cursor CURSOR LOCAL FOR
SELECT ASWTableID, ASWTableName, RuleWhen
from tblASWTable
inner join tblASWField on tblASWTable.ASWTableID = tblASWField.fkASWTableID
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkDataHeaderID = @DataHeaderID
group by ASWTableID, ASWTableName, RuleWhen, InsertOrder
order by InsertOrder
OPEN ASWTable_Cursor
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
WHILE @@FETCH_STATUS = 0
BEGIN
exec spBuildRuleString @DataHeaderID, @RuleWhen, @RuleWhenToExec output, 0
SET @tempStr = 'IF ' + @RuleWhenToExec + ' UPDATE #tmptblUpdateASW SET RuleWhenIsValid=1 ELSE UPDATE #tmptblUpdateASW SET RuleWhenIsValid=0'
EXEC (@tempStr)
IF (SELECT RuleWhenIsValid FROM #tmptblUpdateASW) = 1
BEGIN
set @ErrorMessage = null
exec spASWDataCheck_hardCoded @DataHeaderID, @ErrorMessage output
SET @SQL_Insert = 'INSERT INTO ' + @server + '.' + @ASWTableName + '('
SET @SQL_InsertValues = 'VALUES('
SET @SQL_Update = 'UPDATE ' + @server + '.' + @ASWTableName + ' set '
SET @updateCounter = 0
SET @SQL_Where = ' WHERE '
SET @whereCounter = 0
DECLARE ASWField_Cursor CURSOR LOCAL FOR
SELECT ASWFieldName, AllowASWUpdate, IsPrimaryKey, DataTypeIsNumeric, Data
from tblASWField
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkASWTableID = @ASWTableID and fkDataHeaderID = @DataHeaderID
OPEN ASWField_Cursor
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
select getdate(), 'testposition 2'
WHILE @@FETCH_STATUS = 0
BEGIN
select getdate(), @ASWFieldName, 'testposition 3'
set @Data = replace(@Data, char(39), char(39) + char(39))
if @DataTypeIsNumeric = 0
set @Data = char(39) + @Data + char(39)
set @SQL_Insert = @SQL_Insert + @ASWFieldName + ', '
set @SQL_InsertValues = @SQL_InsertValues + @Data + ', '
IF @AllowASWUpdate = 1
BEGIN
set @SQL_Update = @SQL_Update + @ASWFieldName + ' = ' + @Data + ', '
set @updateCounter = @updateCounter + 1
END
IF @IsPrimaryKey = 1
BEGIN
set @SQL_Where = @SQL_Where + @ASWFieldName + ' = ' + @Data + ' and '
SET @whereCounter = @whereCounter + 1
END
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
END
select getdate(), 'testposition 4'
CLOSE ASWField_Cursor
DEALLOCATE ASWField_Cursor
SET @SQL_Where = LEFT(@SQL_Where, LEN(@SQL_Where) - 4)
SET @SQL_Insert = LEFT(@SQL_Insert, LEN(@SQL_Insert) - 1) + ') ' + LEFT(@SQL_InsertValues, LEN(@SQL_InsertValues) - 1) + ')'
SET @SQL_Update = LEFT(@SQL_Update, LEN(@SQL_Update) - 1) + @SQL_Where
SET @SQL_CheckIfAlreadyExist = 'Update #tmptblUpdateASW set ASWRowAlreadyExists = ' +
'(SELECT * from OPENQUERY(' + @shortServer + ','' SELECT count(*) FROM ' + @ASWTableName + ' ' + replace(@SQL_Where,char(39), char(39) + char(39)) + ' ''))'
Exec(@SQL_CheckIfAlreadyExist)
select getdate(), 'testposition 4'
select getdate(), 'testposition 5'
select getdate(), 'testposition 6'
IF @whereCounter = 0
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Inga primary keys var valda för denna tabellen!')
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) > 1
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Kombinationen av primary keys genererade följande where-sats: ' + @SQL_Where)
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 1 and @updateCounter > 0
begin
EXEC(@SQL_Update)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Update, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 0
begin
EXEC(@SQL_Insert)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Insert, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
END
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
END
CLOSE ASWTable_Cursor
DEALLOCATE ASWTable_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
END
CLOSE Header_Cursor
DEALLOCATE Header_Cursor
UPDATE tblDataBatch set DateToASW = getDate() where DataBatchID = @DataBatchID
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
END
CLOSE Batch_Cursor
DEALLOCATE Batch_Cursor
DROP Table #tmptblUpdateASW
GO
for some reason my loop stops when i = 1 :s
Statement s7 = MySql.connection.createStatement();
for(int i = 0; i < 50 ; i++) {
String test = "INSERT ignore INTO `testtable` (`name`,`Item"+i+"`,`Amount"+i+"`) values ('"+getUsername()+"','"+getItem(i)+"','"+getAmount(i)+"')";
s7.executeUpdate(test);
}
Can anyone help me please???
Hi there!!!
We got problem on sql server 2k,
Sql server stops unknowingly, and all user database has marked as Suspect/Offline,
and later on, after sql server stops, all user database has been detached.
what is going on????