I am working in Powerbuilder and SQL Server 2000. Within the application I dynamically Drop then recreate a view named view_selection_list. When another user accesses any screen using view_selection_list the screen will hang on the statement "If Exists (SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list".
I also went directly onto the database ran select * from view_selection_list from Query Analyzer. It hangs when the original user creating the view is still active. I know that the issue is locking. I don't know how to fix it.
For example ;
String ls_sql
ls_sql="If Exists ( SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list "
Execute Immediate :ls_sql;
ls_sql="Create View view_selection_list as "
Case 'State'
ls_sql+=" Select distinct proj_id,'State - '+proj_state title from project where proj_state='"+is_data+"'"
Case 'Project'
ls_sql+=" Select distinct proj_id,'Project - '+proj_nam title from project where proj_id='"+is_data+"'"
Case 'All Active Projects'
ls_sql+=" Select proj_id,'Project -' +proj_nam title from project where proj_status = 4 and signed_acq_agmt = 'Y' "
End Choose
Execute Immediate :ls_sql;
The SQL Server connection in the application is:
SQLCA.DBMS = "OLE DB"
SQLCA.ServerName="acq"
SQLCA.LogPass ="*******"
SQLCA.LogId = "acq"
SQLCA.Lock = "RU"
SQLCA.AutoCommit = False
SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='FSRFIN103'"
Hi,The SQL server 2000 Server hangs some times. It is not periodic. It isnot specific in any queries, which are taking more time to execute.Because, it is occurring for different types of applications on thesame server on different machines. For the same applications when wehad the SQL Server 7.0 we didn't have any problem.O/S: Windows 2000 advanced serverServer: Dell power edge 2600 4 way server with 2 GB RAMDB: SQL 2000 - Enterprise edition (Normal default installation)- Noservice packs.Are there any server settings to be done...?Thanks in advance..RegardsSeni
I upgraded from SQL server 7.0 to 2000 not long ago, I'm running 2000 SP3, WIN 2K. Things worked fine for several weeks after the upgrade but now I'm experiencing Enterprise Manager hang in the case of one database only, when I try to view the design of a VIEW. I can open the VIEW fine and look at the results, EM only hangs when I try to view design.
I have checked another of the active DBs on my server and it is fine, no EM hang.
Any ideas how to troubleshoot this?
thx
**I just realized that I'm running SP3, not SP4 (corrected above). Correct me if I'm wrong but does MS have a fix for this problem in SP4? I seem to recall a similar issue in the KB.
Hi -We have two SQL 2000 Servers. We have the linked server setup and wecan perform updates and inserts between the databases. But when we adda trigger and insert something into a table, the database hangs. Thereare NO processes blocking or being block in either database. This ONLYoccurs when we have one OS as Windows 2000 Server and the other OS asWindows 2003 Server. This problem does not occur when both servers areWindows 2000. Has anyone ran into anything similar to this???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to execute a stored procedure on SQL server 2000 Developer edition through ODBC. I am using a VC++ client for the same. The stored procedure has 51 parameters. The issue is that the response on method SQLExecDirect is not consistent. At times it will return success or failure based on parameter values, otherwise it just hangs up. The code snippet is like -
It appears to be some locking issue but I am the only user for the database. This code works perfectly fine for other stored procedures. I have tested the parameters on SQL analyzer store procedure execution utility.
I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!
Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.
Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.
Here is what I am doing:
Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,
table2.txt_sort_order
FROM table1 join table2 on table1.status_code = table2.status_code
I've done some searching, asking of friends, and searching every log file and event file I can think of. So now I'm coming here.
Recently I moved some of our databases from an NT4 box running SQL 7 to an Advanced 2000 box running SQL 2000. The web server is still on an NT4 box. It seems that about three times a day or so ASP type files will hang on the webserver. This server hosts different sites and all ASP type files will stop even if some of them hit the old SQL7 server.
Right now I moved the web server back to looking at the SQL 7 machine and things are going fine.
Can anyone offer me a direction to start looking? Why is it working fine to the old stuff but not the new stuff? Is there an issue with NT4 with its IIS trying to talk to an Advanced 2000 with its SQL2000?
I hope someone out there can help, cos i really need it. We have a 2 node cluster + SAN that will be used as our SQL 2000 servers. We have setup the cluster ok, and have tested failover with no problems. However, when we try to install SQL 2000, it just hangs. I have copied the enterprise cd to the local drive, and am installing from there. We run the setup.bat, create the Virtual Server, and assign an ip address. At this point, it just hangs. Upon checking the SQLSTP.LOG file, this is the last entry in the log: Begin Action : DialogShowSdDiskGroups
I have found the Microsoft Technet Article 293788, which gives me a possible soloution, but we dont have any Resource Names or Groups that have the same name.
Can someone PLEASE give me an answer on how to fix this, or at least point me in the right direction.
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
Hi,I have an application that's running fine on development servers (weband database-sql server 2000). I'm updating a record through a thirdparty component but I don't think the component is the problem. What'shappening is that I'm updating fields that are part of view. I'm onlyupdating fields in one table of the view and this works fine in thedevelopment environment.What happens in the production environment when I try to update(using the third party component) I get the following message:"Current recordset does not support updating. This may be a limitationof the provider or of the selected locktype."As an experiment I took the same code but removed the view, leavingonly the table I want to update as the record source. In that case theupdate worked. So it seems that something in the production databasedoesn't like me updating a view. However I can do that in the databasein the development environment.The third party component is dbnetgrid which works fine in thedevelopment environment. I can only conclude it's something about thedatabase that prevents me from updating this same table if it's in aview. I've talked to our DBA but he says there's no difference betweenthe databases. Any ideas would be appreciated.Neil
Hello, without going into the politics of why I'm asking, does anyone have a compiled list of functionality that only sysadmin's could do in 2000 that VIEW SERVER STATE permission opens up in 2005?
From the documentation, so far I've found fn_get_sql (and it's dm view) but I'd really like a complete list.
When using Sql Server Enterprise Manager and viewing a Database / Tables section, most of the tables if not all have a create date of 11/5/2004.Except for one, DNN_Users, has a creation date of 7/10/2007What factor could have caused that create date to have changed?What factors go into the date being set on that column in the database design? Does the date get updated say if I were go go in and change a datatype in a table?
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
I have a Microsoft Cluster running on Server 2003 Entrprise. SQL 2000 8.00.2039 (SP4). 5gb physical memory installed.
With the databases online we run a test failover from the Cluster administrator. It takes about 30-40 seconds and completes without generating any Server Event log errors nor SQL log errors. Everything looks good from an administrative stand point.
However, when we test with running a series of queries to the databases, then failover, we notice that it can take up to 3 or 4 minutes before some of the databases will respond. Connections are not refused, they just sit there.
How can we troubleshoot this or does anyone have a similiar experience with this scenario?
My client is using SQL Server 6.5 on NT 4.0. They have recently began to have their SQL server session freeze intermittently. I have determined that only users that are accessing one particular database are freezing which leads me to a locking problem. What is the best way to determine the locking problem (i.e. event logs, sp_lock, ....)?
I have a DTS that is loading 6 text files into respective tables. Before loading this files the tables are emptied using SQL tasks with the following code: DELETE FROM Table1 WHERE (OMC=?)
OMC is a PK from table1 and ? is a parameter within the DTS. Occasionally the system hangs executing these delete statements for one of the tables (not always the same!) I’m forced to reboot and then DB goes trough a recovery process.
I have a problem where a SQL 2005 server in the morning will deny all connections do to a time out error. Sytem has 4 CPU's in it and sqlservr.exe is utilizing 85% of all CPU's. If I run filemon it does not show activity to any of the SQL databases. I get a login timeout message when I try to connect via the enterprise manager or from a command prompt, even if I bump the timeout delay to 5 minutes. If I restart the SQL Server services all is fine for a few days.
On random occasions, our SQL Server instance will just hang and the users will not be able to use the application until we restart the SQL Server 2005 Service. Anyone have any ideas what we might do to figure out what is going on? The server hung up at 8:00 AM this morning. There was nothing after our midnight backup in the log until we rebooted the server at 8:05 AM.
Any suggestions would be greatly appreciated. Thanks! - Eric-
I need to import several million records into a SQL table from a pipe-delimited text file. I have tried SSMS 'Import/Export' which is slow so I tried bcp from the command line.Importing records into an empty table with no indexes worked a dream. However..Tried to import 13 million records into an indexed table. After almost 50% of the import (6721000 records) bcp appears to have hung and the Server disk is flashing continuously.
We have a SQL server, version 7, that has 4 CPU's. Some of our staff have reports that must be run at random times before cutting checks etc. and these reports bog down the servers CPU's big time, enough so that we can not access the server via Enterprise Manager and at times not even through Query Analyzer. During this time of course other users can not do standard work of saving or accessing the database. Our Processor queue length never seems to go over 1 now. (We recently went from 2 to 4 CPU's do to a 1.4 average queue length).
I have heard that is is possible to limit the number of CPU's that certain query's or events can use. What is the proper terminology for this and can a person do this with limited knowledge? If this can be done during "working hours" so that during off hours query's can use all CPU's, even better.
I wonder if anyone can help.I have a scheduled job running overnight to delete old records for aparticular Db table.The table contains more than half million records and the scriptsimply uses the date field to delete any of the records which have adate older than 7 days. My guess is that there will be some 100,000records which need to be deleted.The job takes ages to run and whilst it is running sql server iscompletely locked and the associated website is effectively offlinebecause of it.I think I know where the problem lies but I don't know why.Whoever created the Db table created a varchar 255 field as theprimary key. The field is then filled with a string of 40 charactersgenerated within the webserver script to have a 'unique' string.Those are the facts. The following is my interpretation.I think the server is unable to cope with not only a character fieldas primary key but also one with so many characters in it. In such alarger table presumably in order to delete the old records it must dosome sort of ordered sort on the Primary Key and it is this that iscausing the problem.I would like to introduce a new field called 'id' make it autonumberedand primary and make the errant field non-primary.So my question is this:Is my analysis correct but more importantly, why? Can anyone give meclear reasoning for it.Also is the solution sound?Table looks like this:clientID intrefID varchar 255 Primary KeyfieldA varchar 512creationDate datetimefieldB varchar 255field C varchar 32Job script:delete from myTable where creationDate < [7daysAgo]Thanks in anticipationBill
Something strange has happened with Reporting Services on the server. When the ReportingServicesService windows service is runnig, it loads CPU (up to 100%) and allocates more and more virtual memory (up to 1,5Gb). There aren't subscriptions for the Report Server and nobody permanently executes reports on it. How can I fix that memory leak?
Log during memory leak: ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. ReportingServicesService!library!4!4/12/2007-15:39:48:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file. ReportingServicesService!servicecontroller!9!4/12/2007-15:39:49:: Total Physical memory: 3220504576 ReportingServicesService!servicecontroller!4!4/12/2007-15:39:49:: i INFO: RPC Server started. Endpoint name ='ReportingServices$MSSQL.3' ReportingServicesService!library!a!4/12/2007-15:39:49:: i INFO: Catalog SQL Server Edition = Enterprise ReportingServicesService!resourceutilities!a!4/12/2007-15:39:49:: i INFO: Reporting Services starting SKU: Enterprise ReportingServicesService!resourceutilities!a!4/12/2007-15:39:49:: i INFO: Evaluation copy: 0 days left ReportingServicesService!crypto!a!4/12/2007-15:42:46:: i INFO: Initializing crypto as user: NT AUTHORITYSYSTEM ReportingServicesService!crypto!a!4/12/2007-15:42:46:: i INFO: Exporting public key ReportingServicesService!crypto!a!4/12/2007-15:42:47:: i INFO: Performing sku validation ReportingServicesService!crypto!a!4/12/2007-15:42:52:: i INFO: Importing existing encryption key ReportingServicesService!servicecontroller!1b!4/12/2007-15:45:46:: i INFO: RPC Server stopped ReportingServicesService!servicecontroller!1b!4/12/2007-15:46:06:: Can't unload domain, trying again ReportingServicesService!servicecontroller!1b!4/12/2007-15:46:23:: Can't unload domain, trying again ReportingServicesService!servicecontroller!1b!4/12/2007-15:46:39:: Can't unload domain, trying again ReportingServicesService!servicecontroller!1b!4/12/2007-15:46:56:: Can't unload domain, trying again ReportingServicesService!servicecontroller!1b!4/12/2007-15:47:13:: Can't unload domain, trying again ReportingServicesService!servicecontroller!1b!4/12/2007-15:47:29:: Can't unload domain, trying again ReportingServicesService!servicecontroller!1b!4/12/2007-15:47:46:: Can't unload domain, trying again
Hi, I just installed SQL Server 2005 Express w/ Advanced Services (9.00.3042.00) on a Windows XP Pro workstation and I am having some trouble. When I launch the Management Studio and try to change or add anything - like creating a SQL Server login, or changing the default folder for databases - the software just hangs on "Executing".
For example, right now I am watching it still say "Executing" after just opening Server Properties and clicking OK without changing anything. It's been doing this for 15 minutes with no sign of it stopping.
I checked the ERRORLOG file and it seems to be completely clean with no errors. Similarly, the Windows Event Logs look clean.
Any ideas or suggestions on how to troubleshoot this issue? I've tried rebooting, reinstalling (and then rebooting) etc...
VisitId Code1 Code2 Code3 Code4 1 N/A Text Code2 Text Code3 N/A 2 Text Code1 N/A N/A N/A 3 n/a n/a *TextCode3* n/a 4 Text Code1 n/a n/a Text COde4
I am sure I Group by VisitId, but do not know the correct function to construct the rest of the Select Query
Create View vw_tblText As Select VisitId, Case(intCodeId=1 Then chrText Else 'N/A' End) As Code1 Case(intCodeId=2 Then chrText Else 'N/A' End) As Code2 etc.. From tblText Group by intVisit
My sql server 7.0 sp2 hangs occasionaly. When looking at the sql connections they are all working and then al of a sudden they go to 0 and the clinets that are searching arewaiting for about 30 seconds or get timed out.
Also the CPU could be at 25% and then goes to 0 then back to 25%
I am also getting the error 17805 Invalid buffer recieved from the client.
this is to do with the ODS.( microsoft info)
Any suggestions as to what is causing this.? or has anyone else encountered this before.
We want to read data from a DB2 view so we set up a linked server in SQL7 (ODBC via Neon's Shadow Direct) but when we issue the following type of query from Query analyzer: select * from openrowset(my_db2link,'select * from test.myview where mycol = ''value''') it just hangs, and we can't kill the process (the Enterprise manager 'KIll Process' button has no effect!) - the only way we can get rid of these queries seems to be to stop/restart SQL Server When we look at current activity in Enterprise manager the process seems to be waiting for a resource (either MISCELLANEOUS or PAGEIOLATCH) The query works OK if I run it on the server using Shadow direct, so the error must be between SQL Server and Shadow direct Has anyone seen this error before? Thanks in advance, John
I have a procedure on SQLSERVER DB1 where i insert into few tables on SQL SERVER DB1 and delete a table on remote server SQL SERVER DB2 via linked server. Unfortunately, the delete is taking for ever. Procedure never completes. Data in that table is close to 500 records.
I have delete statement like Exec(delete linkserv.onedb.stg.tab1)
Later i modified the delete query as below but no luck
DELETE OPENQUERY(linkserv, 'Select * from onedb.stg.tab1')
What can i do here to get this fixed? Please see linked server properties below Enable promotion of distributed transactions for RPC:True Use Remote Collation: True All other properties : False
How can i debug my SQL Server procedure and find the issue. I came to know that i do not have permission to use debug feature on SSMS.
We have SQL 2000 running on Server 2003. The server has 4 GB RAM, RAID 1 and has the /3GB switch in Boot.ini. We have 6 databases, the largest is about 14 GB. Until recently I could use Enterprise Manager to perform database backups to a folder on the same server but now the operation fails. After selecting the location for the backup and clicking OK, the hard drive light comes on but goes out after 15 - 30 seconds. The progress bar does not move. I've waited and waited but nothing further happens and the server doesn't respond. The only solution is to push the power button and let the server re-boot. Very occasionally the backup will run for a minute or two and the progress bar might get to about one third of the way across, (in which case the destination file will show up with more than 0 bytes), but it never completes. I can find nothing in the event log relating to this problem. As far as I am aware, nothing has changed since the last successful backup. Backups to tape using Retrospect appear to work fine but I haven't done a test restore. Does anyone have any ideas as to what I can try to get the backups working again?