I am looking into a problem where someone has reported their SQL Server Express has stopped working after the system goes into hibernation.
Has anyone heard of Windows Services not starting back up after hibernating or standby?
I have tested this numerous times and not run into the problem.
HI all,
i use sql server ce for an application i made for a customer and i deploy the data file on the flash card.
When the device goes on hibernate mode or shuts down because it runs out of power, i have corrupt databases on some
occassions. Its hard to reproduce and happens very randomly. The data files are pretty large for such a device (25-30 MB) and the device has 64 - 128 MB of storage
What can i do to avoid this??
I tried to hook on the OnHibernate event of the application object and shutdown the application and close all DB connections but it did not work.
If you can, post any link on tutorials and best practices for such a thing.
Thanks in advance
Right now I am running an ASP.Net 2 app that talks to a MS SQL 2005 server. All of the Inserts and Updates are done with NHibernate ORM, and there are a few places where we use the DB as a datasource for gridviews.
Our security team asked us to have our sensitive data encrypted, which consists of a single table (one column really).
Has anyone here implemented encryption through Hibernate or know of a good work around?
Alternately, does anyone know where documentation is for SQL encryption so I could atempt to replicate it in .Net, in my Get and Save methods for Hibernate.
Thanks.
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!
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????
Hello,
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
Hi,
I have transactional replication set up between two dedicated servers. Server A is the PDC and Server B is a BDC (they are both Win2000 boxes). Both the servers are brand new, and replaced the two that were running like clock work (replication wise) for the last 12 months. I never had this problem with the old servers....
When the servers are shut down (as the case was a couple of weeks back with a power failure) or just recently when they were move to another room. Both servers boot up at the same time. Server B (which is the server holding the db being replicated) boots quicker and as a result replication fails and is then 'sucessfully stopped'. Unless I am aware of the server being rebooted and can monitor this potential problem, within 2 days the logfile grows to large and everything comes to a crashing halt.
I just remove replication, truncate and shrink the log, reset replication and we're away.... BUT I really need to know why it is happening in the first place. I figure there must be a setting that I have forgotten about or something.
Both servers are Win2000 (SP4) and SQL2000(SP3a).
Any help would be appreciated.
Thanks
Casper
I have just installed SQL Server 2005. The machine had an expired version of SQL Express on it which I removed. Now when i go to open the Management Studio it says it can't connect. When I check the service, it is set to automatic using the local account but is not running. When I start it, it throws an error stating that it started and then stopped. The first error record in the log file states:
Server TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.
Could this be causing the problem? How do I check to see what is using 1433? In general, what the heck did I do wrong during the install? One article said something about the new installation thinking it was still the expired version. Could there be something left in the registry from the old version of Express causing this?
Thanks in advance for the help
SQL Server 2005 installed on Windows Vista Ultimate
When I try to start the agent, it return with the message that the service started and then stopped. On investigation I found that the Windows Event Log service won't start, returning the error:
"error 4201: the instance name passed was not recognized as valid by a WMI
data provider"
(I have posted this on Windows Vista forums but had no response).
Thanks for your help
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.
I will appreciate some help here.
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.
Many Thanks
Muzaffar
Network Admin.
Carrier - Saudi Arabia.
Hello everyone. I'm having a problem with the sql server agent occasionally not executing its scheduled jobs, and unless the sql server agent is restarted they will not execute according to their schedule. I've read many posts on people having problems with running a package manually and then not being able to run it in a job, although that is not my problem since these are jobs that have all run successfully historically, and only until the agent is restarted do they start working again. It is the strangest thing.....we have many different jobs on this server and all of them will just mysteriously stop being executed. Two other things of note, the agent and server processes both run on AD accounts, and we receive this error:
SQL Server Scheduled Job 'X' - Status: Failed - Invoked on: 2007-10-27 02:00:00 - Message: The job failed. Unable to determine if the owner (ADadacct) of job 'X' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).
Any help would be greatly appreciated.
I have a test server running both 2000 and 2005 Reporting Services. Each day I have to restart the Report Server Windows service for the 2005 Report Server. This is the error I get when I try to access the Report Manager
The Report Server Windows service 'ReportServer$TSQL4' is not running. The service must be running to use Report Server. (rsReportServerServiceUnavailable) Get Online Help
After I start the Report Server Windows service using the Report Services Configuration tool I can access the 2005 Report Manager without errors. The windows service start up type is set to automatic.
I can not find why the 2005 version keeps going down.
I'm using SQL server 2014 and all of a sudden the server stops working and I have to go to the configuration manager to start it. Though the start mode is set to automatic, this still happens and I just don't know why.
View 4 Replies View Related
I was trying to migrate an ASP application from Access to SQL Server 2005 express, and found surprisingly that some code stops working which would seem to be independent of the data source. For example:
qtext = "SELECT MainText, TextType FROM MessageText WHERE [etc. etc. -- the query is OK]"
rsMain.open qtext, dbcon, , , adCmdText
if not (rsMain.BOF and rsMain.EOF) then
rsMain.MoveFirst
do while not rsMain.EOF
select case rsMain.fields(1).value
case 1
session("headline") = rsMain.fields(0).value
case 2
introtext = rsMain.fields(0).value
case 6
helptext = rsMain.fields(0).value
end select
rsMain.MoveNext
loop
end if
rsMain.Close
"rsMain" is an ADO recorset which defaults to a forward-only cursor as opened above; dbcon is an ADO database connection. Nothing fancy; and it has been working for years as expected with Access. But the recordset seems to behave differently when SQL Server is the data source (even though the whole point of ADO is to provide a level of abstraction, and I expected it not to change.) In the code above, checking the value of "rsMain.Fields(1).value" causes "rsMain.Fields(0).value" to disappear or become inaccessible. As a simple debug exercise, I tried response.writing "rsMain.Fields(0).value", a block of text, prior to the select case block, and it works fine once (as long as rsMain.Fields(1).value has not been accessed), but then a second write comes up empty.
Is it reasonable to expect that ADO recordsets and cursors function differently if SQL server is the data source vs. Access? Is this documented anywhere?
I have a report that uses three tables from a database. As long as I only use two of the tables, it runs fine. I need the data from the third table for a line chart. So of course there is a great deal of data in the third table. I have a where clause for start date and end date. Is there a way I could only search the third table after I know what I need from it? Or
View 6 Replies View RelatedDear Helpers,
I can not setup SQL server 2005 express and the full trial version as well.
The setup progress stops at "setting file security", and nothing happens. I dont even get an error message.
This is very annoying. I have local administrator access, so it should work. Op system: Windows XP professional.
Machine: Hp Compaq dc7700p, 1 GB RAM, 80 GB HDD
Thanks for your help in advance.
Hi,
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
Thanks,
Darrell Young
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 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???
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
View 1 Replies View RelatedI 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?
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?
View 5 Replies View RelatedI'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.
View 7 Replies View RelatedHi 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.
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?
Thanks
Axel
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
ALTER PROCEDURE [dbo].[create_table1_tmp] AS
/* table1 variables */
declare@v_ARCKEY int
declare@v_INVKEY int
declare@v_ITEM char (15)
declare@v_BRAND char(15)
declare@v_PRICE decimal(12, 2)
declare@v_QTYORD decimal(12, 3)
declare@v_QTYSHP decimal(12, 3)
declare@v_CTCKEY int
declare@v_SOMKEY int
declare@v_ORDATE datetime
declare@v_FNAME char (15)
declare@v_CONTACT varchar (20)
declare@v_TITLE varchar (25)
declare@v_SALUT char (4)
declare@v_DEGREE char (6)
declare@v_SALESMN2char (3)
declare@v_TTL_CODEchar (4)
declare@v_SPECIALTYvarchar (30)
declare@v_COMPANYvarchar (35)
declare@v_ADDRESS1varchar (50)
declare@v_ADDRESS2varchar (50)
declare@v_CITYvarchar (20)
declare@v_STATEchar (10)
declare@v_KEYCODEchar (10)
declare@v_ZIPchar (10)
declare@v_COUNTRYchar (2)
declare@v_ARCSOURCEchar (5)
declare@v_SLSVOLchar (1)
declare@v_TYPEchar (8)
declare@v_NUMDRSdecimal(9,0)
declare@v_BEDSIZEdecimal(4,0)
declare@v_NUMLIVESdecimal(9,0)
declare@v_CODEchar (3)
declare@v_SUBKEYint
declare@v_SUBTYPEchar (1)
declare@v_STARTDATEdatetime
declare@v_TERMdecimal(2, 0)
declare@v_STATUSchar (1)
declare@v_STATDATEdatetime
declare@v_XRENEWEDdecimal(2, 0)
declare@v_EXPDATEdatetime
declare@v_SHPAMTdecimal(12, 2)
declare@v_SOMSOURCEchar (5)
declare@v_PMETHchar (1)
declare@v_EXTPRICEdecimal(12, 2)
declare@v_CTYPEchar (5)
declare@v_CTCKEY_SBSUBS int
declare@v_ACRONYMchar (8)
declare@v_PONUMvarchar (20)
declare@v_EMAILvarchar (50)
declare@v_ARCPHONEVARCHAR (20)
declare@v_ARCFAXNOVARCHAR (20)
declare@v_CMCPHONEVARCHAR (20)
declare@v_CMCFAXNOVARCHAR (20)
declare@v_DOFAXTINYINT
declare@v_DOPHONETINYINT
declare@v_DORENEWTINYINT
declare@v_DOMAILTINYINT
declare@v_DOUPDATETINYINT
declare@v_UPDONLYTINYINT
declare@v_EMAIL_INFOTINYINT
declare@v_EMAILPROMOTINYINT
declare@v_DISCdecimal(7,3)
declare @v_EUPDATETINYINT
/* processing Variables */
declare @v_tmpint
declare@v_amtdecimal(9,3)
declare@v_loopint
declare@v_arckey_tmp INT
declare@v_ctckey_tmp INT
declare@v_invkey_tmp INT
declare@v_subkey_tmp INT
declare@v_arcDORENEW TINYINT
declare@v_cmcDORENEW TINYINT
declare@v_sbsDORENEW TINYINT
declare@v_arcDOFAX TINYINT
declare@v_cmcDOFAX TINYINT
declare@v_arcDOMAIL TINYINT
declare@v_cmcDOMAIL TINYINT
declare@v_arcDOPHONE TINYINT
declare@v_cmcDOPHONE TINYINT
/* cursors for retrieving online data*/
declarev_mast cursor for
select /*+ INDEX_COMBINE(arc) */
som.arckey arckey,
sot.invkey invkey,
sot.item item,
sot.price price,
sot.qtyord qtyord,
sot.qtyshp qtyshp,
som.ctckey ctckey,
som.somkey somkey,
sot.ordate ordate,
arc.phone arcphone,
arc.faxno arcfaxno,
arc.salesmn2 salesmn2,
arc.country country,
arc.source arcsource,
arc.slsvol slsvol,
arc.type type,
arc.specialty specialty,
arc.numdrs numdrs,
arc.bedsize bedsize,
arc.numlives numlives,
arc.code code,
arc.dorenew arcdorenew,
arc.company company,
arc.address1 address1,
arc.address2 address2,
arc.city city,
arc.state state,
arc.zipzip,
sub.eupdate eupdate,
sub.doupdate doupdate,
sub.subkey subkey,
sub.subtype subtype,
sub.startdate startdate,
sub.term term,
sub.status status,
sub.statdate statdate,
sub.xrenewed xrenewed,
sub.dorenew sbsdorenew,
som.shpamt shpamt,
som.source somsource,
som.pmeth pmeth,
sub.ctckey ctckey_sbsubs,
sub.updonly updonly,
sot.disc disc,
arc.dofax arcdofax,
som.ponum,
arc.domail,
arc.dophone
from sotran sot,somast som, arcust arc,sbsubs sub
where (som.arckey != '121449' and som.arckey != '1364166')
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
--select fname, contact, title, salut, degree, phone, faxno, ttl_code, dorenew,
--ctype, email, dofax, domail, email_info, emailpromo, docall
--from cmctac
--where ctckey = @v_ctckey
--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
-- remove all but the newest orders
if @v_arckey = @v_arckey_tmp and @v_ctckey = @v_ctckey_tmp and
@v_invkey = @v_invkey_tmp and ((@v_subkey is null and
@v_subkey_tmp is null) or (@v_subkey = @v_subkey_tmp))
set @v_tmp = null
else
Begin
begin tran t5
set @v_arckey_tmp = @v_arckey
set @v_ctckey_tmp = @v_ctckey
set @v_invkey_tmp = @v_invkey
set @v_subkey_tmp = @v_subkey
commit tran t5
begin tran t6
insert into table1_tmp
(
arckey, invkey, item, brand, price, qtyord, qtyshp, ctckey, somkey,
ordate, fname, contact, title, salut, degree, salesmn2, ttl_code,
specialty, company, address1, address2, city, state, keycode,
zip, country, arcsource, slsvol, type, numdrs, bedsize, numlives,
code, subkey, subtype, startdate, term, status,
statdate, xrenewed,expdate, shpamt, somsource,
pmeth, extprice, ctype,ctckey_sbsubs, acronym,
ponum, email, arcphone, arcfaxno, cmcphone, cmcfaxno,
dofax, dophone, dorenew, domail, doupdate, updonly,
email_info, emailpromo, disc)
values (
@v_arckey, @v_invkey, @v_item, @v_brand, @v_price, @v_qtyord, @v_qtyshp,
@v_ctckey, @v_somkey, @v_ordate, @v_fname, @v_contact, @v_title,
@v_salut, @v_degree, @v_salesmn2, @v_ttl_code, @v_specialty, @v_company,
@v_address1, @v_address2, @v_city, @v_state, @v_keycode,
@v_zip, @v_country, @v_arcsource, @v_slsvol,
@v_type, @v_numdrs, @v_bedsize, @v_numlives, @v_code,
@v_subkey,@v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_expdate, @v_shpamt, @v_somsource,
@v_pmeth, @v_extprice, @v_ctype, @v_ctckey_sbsubs, @v_acronym,
@v_ponum, @v_email, @v_arcphone, @v_arcfaxno, @v_cmcphone,
@v_cmcfaxno, @v_dofax, @v_dophone, @v_dorenew, @v_domail,
@v_doupdate, @v_updonly, @v_email_info, @v_emailpromo, @v_disc
)
commit tran t6
End
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
end --while loop
close v_mast
deallocate v_mast
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.
Thanks for any ideas,
Brian
I have a stored procedure which does a simple select joining 3 tables.
This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.
After re-compiling the procedure, it worked fine as before.
Does anyone know of any reason why a procedure would need recompiling like this?
We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?