Hello,
I have 2 servers with the same software (win2k, sql 2000 sp3) and the same data on them. The older server is working fine, but the new one that I am trying to use will run fine for a various amount of time and then stop responding. I am confused because when it does this I can pull up the task manager and see that there is plenty of memory and cpu left, and there are no error messages up on the screen. The only way that I can get the server to respond again is to restart the system. The problem never happens at the same time of day and it doesn't matter how long it has been since the last reboot, could be 5 minutes, could be 5 hours. If anyone has any ideas on what could be causing this, any replies are appreciated. The new system is a dell 2600, 2G Ram, Xeon processor, win2k.
Can anyone help me? My brand new installation of SQL Server 6.5 running on Windows 2000 suddenly stops working for about 6 minutes, in this periods no one can use SQL 6.5 services because it stops responding. My logs is show below:
2002/04/08 15:50:22.60 ods Error : 17832, Severity: 18, State: 0 2002/04/08 15:50:22.60 ods Unable to read login packet(s). 2002/04/08 15:50:24.50 ods Error : 17832, Severity: 18, State: 0 2002/04/08 15:50:24.50 ods Unable to read login packet(s). 2002/04/08 15:50:26.79 ods Error : 17832, Severity: 18, State: 0 2002/04/08 15:50:26.79 ods Unable to read login packet(s). 2002/04/09 07:10:21.48 ods Error : 17832, Severity: 18, State: 0 2002/04/09 07:10:21.48 ods Unable to read login packet(s). 2002/04/09 07:53:33.76 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:53:33.76 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname '2M_ROSANNA'. 2002/04/09 07:53:33.76 ods OS Error : 232, 2002/04/09 07:53:33.78 spid75 Error : 1608, Severity: 21, State: 2 2002/04/09 07:53:33.78 spid75 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:53:41.10 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:53:41.10 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_SANDRA'. 2002/04/09 07:53:41.10 ods OS Error : 232, 2002/04/09 07:53:41.10 spid80 Error : 1608, Severity: 21, State: 2 2002/04/09 07:53:41.10 spid80 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:55:42.20 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:55:42.20 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_SANDRA'. 2002/04/09 07:55:42.20 ods OS Error : 232, 2002/04/09 07:55:42.20 spid53 Error : 1608, Severity: 21, State: 2 2002/04/09 07:55:42.20 spid53 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:57:09.40 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:57:09.40 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'FE_FERTILIDAD'. 2002/04/09 07:57:09.40 ods OS Error : 232, 2002/04/09 07:57:09.40 spid91 Error : 1608, Severity: 21, State: 2 2002/04/09 07:57:09.40 spid91 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:57:09.40 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:57:09.40 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'SF_ROSA'. 2002/04/09 07:57:09.40 ods OS Error : 232, 2002/04/09 07:57:09.40 spid11 Error : 1608, Severity: 21, State: 2 2002/04/09 07:57:09.40 spid11 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:57:36.04 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:57:36.04 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_MILAGROS'. 2002/04/09 07:57:36.04 ods OS Error : 232, 2002/04/09 07:57:36.04 spid60 Error : 1608, Severity: 21, State: 2 2002/04/09 07:57:36.04 spid60 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
The problems begin on 2002/04/09 at 7:53am. and it respond again at 7:57, there's no need to restart the service.
Hi All, I am having exchange server 2003. With following details.
1.Operating System 2003 standerd with sp2. 2.Exchange server 2003. Service pack don't know ( How to check)
From last 2 months, every two days my exchange server stops responding to clients.
When I check the services, all the Exchange related services are ON. ( they are running fine).
Then I use to Restart All excahnge related services, i.e Microsoft Exchange Information Store & Ms Exchange System Attended service and things starts working fine again.
This I have to do every 2 days.
I don't know why this is happening.
Can anyone assist me to resolve as it is getting critical day-by-day. Also How can I check which service pack of exchange is installed.
I've got a couple of jobs who have a odbc connection to a AS400 machine. But when these jobs run they won't stop anymore. I've got to stop these jobs manually so that the next day the jobs can start again as scheduled. The jobs did run all the packages succesfully. Does somebody know how this is possible? It did work fine but since a couple of weeks they just won't stop anymore. I hope you can help me! :S
This happens only on multiprocessor machines. SQL Service pack 3. The only way to fix it is to restart the SQL service. If you have a performance tool based on that value, like Idera's SQLdm- sorry, it will show zero.
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.
I've noticed some strange behavior on my SQL Server. A little background first:
This server is strictly used for SQL Server Active/Passive cluster Windows Server 2003 Enterprise w/ SP1 SQL 2005 Enterprise w/ SP2 8GB of total memory in the server, SQL Server has min: 4GB, max: 6GB Disk drives are on a SAN 4 hyperthreaded, Xeon 1.4GHz proccessors
Now, the issue I'm seeing...
Every once in a while, my users will start to complain about application slowness and we'll start getting application timeouts. I'll look at my SQL server and my server stops responding for a time period of 8-9 seconds. One minute after the server 'wakes up', the process starts again where it will not respond for 8-9 seconds. The clock on the server stops refreshing then will jump to the current time, and in perfmon, there is an actual break in the counters. This does not happen at all times throughout the day, but I do need to find out a solution for this behavior.
During this 8-9 seconds, all processing is put on hold and continues when the server 'wakes up'. Any new connections to the server are accepted during this timeframe, but have to wait through this 'dead' time as well.
Any and all comments, suggestions, concerns, or things I could be looking at are appreciated.
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?
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!
Hello, I am getting very intresting problem, Server is working fine. I am able to connect to server through VB,VC++ applications. Whenever I am connecting through ISQL/W it is working properly. But when I open Enterprise manager and click on ther server i wish to connect it is not responding and I am getting white screen (NOT responding Screen). But at the same moment I am able to connect through other sources like ISQL/W and VB application. Anybody knows what is the reason?
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
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
My sql agent mail suddenly stops working. The mails are usually going to the people. But once in a while I have this wierd problem of mails not reaching the recipients. Happens once in 2-3 weeks, starts working fine if I restart the server. When I open the Outlook client, the outbox is also empty, but still the mails are not gone. If I Test the mail profile in the sql agent properties, it gives time out error. Everything becomes okay if I restart the server.
OS: Win 2K sp4 Server : SQL 2000 SP3 Account : Verified that the account is in Administrator Group There is only one MAPI32.DLL file and is in system32 folder.
The wierd part is everything works if I restart..any help would be much appreciated.
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????
Dear Group,I am tring to use a command that calls the server to fill an adapter, itnever seems to get to the adapter, command and the server either times outor does not respond. The timeout is set at 10 hours. I am using VisualStudio to acces MS SQL - Server.I think I have all the rights and permissions set correctly. Also, I haveused this command to fill other adapters and tables.Does anyone have a suggestion.Jeff Magouirk
The report server was running ok until this morning. Its just giving a "The report server is not responding. Verify that the report server is running and can be accessed from this computer." message when trying to open the report manager.
Any ideas?
In the SQLDUMPER_ERRORLog.log file there is this..
I have an SSISS package running in a job step, the job is owned and has to be run by a non-sysdamin SQL login. I have created a new credential (windows autrhentication, sysadmin priviledges) , mapped it to proxies 'SSISS' and 'Operating systems (CmdExec)' and have assigned the job step to run as that credential. Yet I'm getting the following error message:
Unable to start execution of step 1 (reason: Error authenticating proxy DomainUserName, system error: Logon failure: unknown user name or bad password.). The step failed.
I have a very weird issue in my latest package. I run it and it just randomly stops. When I watch it in debug mode or not in debugging a command prompt window will flash for an instant (too fast to read) and then the package will stop. It stops inside of a for each loop and the "on completion" is never fired for the loop. I never receive any errors - its just like someone hit the stop button. Sometimes it will process hundreds of files before stopping, other times only a few. (And its not stopping on the same file each time.. it doesn't appear to be related to the file at all)
Any ideas what could be going on? How to troubleshoot?
When I am browsing the http://localhost/reports i am getting the errr The report server is not responding. Verify that the report server is running and can be accessed from this computer.
This error came when i just in the IIS in reports(Right Click) application change the asp.net(Edit Configuration) on tab authentication from WINDOWS to FORM while though i reverted back mine changes to windows its stated giving me the above exception
Please provide me the solution .All the reports are stopped due to mine adventure..
Have a task that has 120 tables (components) that I am running in debug mode. Just over half of the components run which takes btrieve db and converts into a sybase db. When it stops running there are a few components that are yellow, the components which completed are green and the rest are still white because they have ran yet. The problem is there is not a message to indicate as to why it stopped. I've broken up the task into two tasks and also tried making two projects. The same situation happens at the same point. Our dbas have checked the database to ensure that's fine and it is. Is there some sort of limitation in how many components can be run in debug mode?
I've been coding for a few weeks now, building an ASP.NET application. ran the aspnet_regsql.exe wizard and it created my table and procedures correctly within the live SQL 2005 server being run by my host (ASPNIX.COM). I've been able to run my app just fine locally saving to my remote SQL server. However, now that I've moved my code onto my hosted server, my app stopped working. You would think this would "jus work" since I've been using the same SQL server throughout...BUT NOoooo! I've scoured the net trying to find any hint about what might be happening. The ONLY thing I've been able to find is a story in the May issue of asp.netPRO that says "Once the scripts have been executed, grant the user account used to access the database from the Web application Execute permissions on all the new stored procedures and Selection permissions on the views that were created"This may be my problem, but I haven't figured out how to accomplish this. Within the SQl Server Management Studio Express, I can pull up the properties of each stored procedure and place a check next to my "USER" account. However it does not appear to be saved.My symptoms at this point are:It will not log me in under accounts I'd already created.
Hi i have a page in which stock can be allocated, there are two boxes which have a product serial number start range and a product serial number end range, when these boxes are filled the "allocate" button is then clicked and the product will then be allocated the serial numbers. What i want to happen is that when the start and end ranges have been entered into the text boxes it will fail if any number within the range has already been allocated previously. E.g
Start Range
End Range
So lets say in the start range text box 15 is entered and in the end range 25 is entered, however 18 has already been allocated previously, this will then bring up a message saying please select another range; I have done the following so far;private void ValidateRange() { //String strSql;SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["strConnectionString"]); String strSql = "SELECT SqlCommand dbCommand4 = new SqlCommand(strSql, conn); dbCommand4.Parameters.Add("@Start_RangeID", txt_Start_Range.Text);dbCommand4.Parameters.Add("@End_RangeID", txt_End_Range.Text); dbCommand4.Connection = conn; conn.Open();SqlDataReader myDataReader = dbCommand4.ExecuteReader(); myDataReader.Read();if (txt_Start_Range.Text == Convert.ToString(myDataReader["Serial_No"])) {lblRange.Text = "Please enter a different range a portion of the selected values have already been allocated"; //Response.Redirect("Selectedfilm.aspx"); } else {Allocate(true); }
conn.Close();
}
My problem is constructing the select statement, thanks.
Hi, Our app uses an NT4 SQL 2k box and a Win2k IIS box. To fulfill the text searching requirements we've had to use dynamic SQL (created within a large SP). This query searches about 8000 rows in a flat table (which we create and keep up to date using a scheduled SP which trawls the underlying tables). It's a complicated SP, and can return all 8000 rows (I think we're winning the battle to have this capped though). When we get about 50 users hitting the app and doing searches simultaneously, performance drops severely (20 secs+ for a query rather than ~2 secs), and we get a lot of errors - which report that: Source: .Net SqlClient Data ProviderMessage: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. We also get a lot of Invalid Viewstate errors, which i'm totally baffled by, and the odd IIS Access Denied error - as if authentication (NTLM) is timing out. Neither the Web not the SQL machines are being hit very hard - the web server in particular is not under any strain whatsoever. It doesn't appear to be related to connection pooling, from the logged error messages we're getting. Does anyone have ANY suggestions as to where I should be looking?!
Hi All, Do anybody know if it's possible to have an SQL 2005 server deal with and out of office reply from an email its send via the new Database Mail? My application sends mail to users, giving them task instructions via Database Mail, however if they have thier out of office set true, I just get a reply msg in the profiles inbox. I would like the SQL server to know that they are out of office. I can recogise the replys by the subject and I have control over the format of the replies, so its just case of getting the SQL server to read msg in the inbox. Thanks Gary T
We are currently trying to install SQL Server 7 Enterprise edition using MS SELECT CDs. The install wizard stops at the beginning telling us that the used CD can only be used to install SQL Server clients with this computer although we used exactly this CD-ROM to install all other SQL Servers, too.
Did anybody have the same problem and knows what the reason for the abortion is?
I have a stored procedure that is calling a cursor to populate some variables it then uses those variable to get more information and then inserts that info into a final table. The estimated number of records that it should insert is around 2 million. The procedure stops after about 6000 records inserted. It still apears to be running but in fact is not. Can anyone help? I have also attached the code.
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
and (som.sotype not in ( select value from table1_param where name = 'sotype') or som.sotype is null)
and (som.sostat not in ( select value from table1_param where name = 'somstat') or som.sostat is null) and arc.arckey = som.arckey
and (arc.code not in ( select value from table1_param where name = 'code') or arc.code is null) and (arc.slsvol not in ( select value from table1_param where name = 'slsvol') or arc.slsvol is null)
and arc.foreign_ = 0 and sot.somkey = som.somkey
and (sot.sostat not in ( select value from table1_param where name = 'sotstat') or sot.sostat is null) and sot.item > '0100' and sub.subkey =* sot.subkey order by arc.arckey,som.ctckey,sot.invkey,sub.subkey,sot.rq date desc
-- BEGIN PROCESSING THE CURSOR DATA HERE
OPEN v_mast
--pull all somast records where custno isn't 121449 or 1364166 --and sotype filtered and sostat isnot filtered
set @v_loop = 0 fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord, @v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country, @v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives, @v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city, @v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status, @v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs, @v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone while @@fetch_status = 0--for v_rec in v_mast loop Begin set @v_loop = @v_loop + 1 set @v_expdate = dateadd(month,@v_term,@v_startdate) - 1 set @v_keycode = ''
--select company, address1, address2, city, state, zip --from cmcship --where ctckey = @v_ctckey
--if (@@rowcount = 0) --set @v_tmp = null --else --begin begin tran t1 set @v_company = (select top 1 company from cmcship where ctckey = @v_ctckey) set @v_address1 = (select top 1 address1 from cmcship where ctckey = @v_ctckey) set @v_address2 = (select top 1 address2 from cmcship where ctckey = @v_ctckey) set @v_city = (select top 1 city from cmcship where ctckey = @v_ctckey) set @v_state = (select top 1 state from cmcship where ctckey = @v_ctckey) set @v_zip = (select top 1 zip from cmcship where ctckey = @v_ctckey) commit tran t1 --end
--select company, address1, address2, city, state, zip --from cmcadd --where ctckey = @v_ctckey
--if (@@rowcount = 0) --set @v_tmp = null --else --begin begin tran t2 set @v_company = (select top 1 company from cmcadd where ctckey = @v_ctckey) set @v_address1 = (select top 1 address1 from cmcadd where ctckey = @v_ctckey) set @v_address2 = (select top 1 address2 from cmcadd where ctckey = @v_ctckey) set @v_city = (select top 1 city from cmcadd where ctckey = @v_ctckey) set @v_state = (select top 1 state from cmcadd where ctckey = @v_ctckey) set @v_zip = (select top 1 zip from cmcadd where ctckey = @v_ctckey) commit tran t2 --end
--if (@@rowcount = 0) --RAISERROR ('cmc not found', 16, 1) --else --Begin begin tran t3 set @v_fname = (select top 1 fname from cmctac where ctckey = @v_ctckey) set @v_contact = (select top 1 contact from cmctac where ctckey = @v_ctckey) set @v_title = (select top 1 title from cmctac where ctckey = @v_ctckey) set @v_salut = (select top 1 salut from cmctac where ctckey = @v_ctckey) set @v_degree = (select top 1 degree from cmctac where ctckey = @v_ctckey) set @v_cmcphone = (select top 1 phone from cmctac where ctckey = @v_ctckey) set @v_cmcfaxno = (select top 1 faxno from cmctac where ctckey = @v_ctckey) set @v_ttl_code = (select top 1 ttl_code from cmctac where ctckey = @v_ctckey) set @v_cmcdorenew = (select top 1 dorenew from cmctac where ctckey = @v_ctckey) set @v_ctype = (select top 1 ctype from cmctac where ctckey = @v_ctckey) set @v_email = (select top 1 email from cmctac where ctckey = @v_ctckey) set @v_cmcdofax = (select top 1 dofax from cmctac where ctckey = @v_ctckey) set @v_cmcdomail = (select top 1 domail from cmctac where ctckey = @v_ctckey) set @v_email_info = (select top 1 email_info from cmctac where ctckey = @v_ctckey) set @v_emailpromo = (select top 1 emailpromo from cmctac where ctckey = @v_ctckey) set @v_cmcdophone = (select top 1 docall from cmctac where ctckey = @v_ctckey) commit tran t3 --end
--select acronym, brand from invhead --where invkey = @v_invkey
--if (@@cursor_rows = 0) --set @v_tmp = null --else --begin begin tran t4 set @v_acronym = (select top 1 acronym from invhead where invkey = @v_invkey) set @v_brand = (select top 1 brand from invhead where invkey = @v_invkey) commit tran t4 --end
set @v_amt = ((@v_qtyshp+@v_qtyord)*@v_price *(100-@v_disc))*.01
if @v_amt < 0 set @v_extprice = round(@v_amt,2) else set @v_extprice = round(@v_amt,2)
if (@v_arcdomail = 0 or @v_cmcdomail = 0) set @v_domail = 0 else set @v_domail = 1
if (@v_arcdofax = 0 or @v_cmcdofax = 0) set @v_dofax = 0 else set @v_dofax = 1
if (@v_arcdophone = 0 or @v_cmcdophone = 0) set @v_dophone = 0 else set @v_dophone = 1
if ((@v_arcdorenew = 0 or @v_cmcdorenew = 0) or @v_sbsdorenew = 0) set @v_dorenew = 0 else set @v_dorenew = 1
I am trying to connect sql server on my local system to view databases, or any server on LAN. But Enterprise Manager is not responding to show me databases. Even my application takes a long time to get connection to database. Would you please help me out. That where is the problem?
I am trying to move SQLMail from a standard SQL 6.5 server to a virtual clustered SQL Server. The exchange profile has been set up and the services are running on a domain login. Exchange is also running as a clustered service which is being used successfully from the standalone SQL Server with SQL Mail.
However, when the SQL Mail service is started on the Clustered SQL Server, the service starts for about 5 seconds and then stops. The logs report that SQL Mail started but no messages are recorded to state that why the service has stopped again.
We are using - MSSQL Server 2000 on windows 2000 advanced server. PB 7.0 for client server front end tools.
Prior to few days our application works fine, rightnow it get slow or not responding after few (4/5) transaction. We don't know wht it is, If anyone has same experience so that is helpfull for us.
