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.
View 7 RepliesMy 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, ....)?
Hi ppl
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.
Any idea what might be wrong?
Thanks in advance
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.
View 4 Replies View RelatedOn 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-
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
Forgive my lack of knowledge on SQL here...
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.
Thanks,
Mike Gilbert
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
View 2 Replies View RelatedHi,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
View 3 Replies View RelatedSomething 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...
Is there a newer build for this?
Thanks
-JS
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 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'"
Thank You
Stanley
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?
View 14 Replies View RelatedI have an SSIS package that executes in about 1:20min from Visual Studio on my local machine. While executing, my machine is somewhat unresponsive.
When I deploy the package to the database server -- the very same database server that I am accessing from my local machine -- the package executes but eventually hangs. It appears to be running out of memory, and I usually have to kill the process to get the machine to respond. While it's hanging, the machine is unresponsive to all users. The hardware (including memory) is identical between my local development box and the server.
How should I troubleshoot this? I've tried deploying the package to MSDB, file system, running from dtexec, and running from dtexecui. This is very frustrating!
Thanks for any help.
--j
Hello,
I have an SSIS package that contains a source to DB2 using the Native OLE DBIBM OLE DB Provider for DB2 in the connection mananger. The connection requires a specific user name and password. 'Test Connection' passes, and I am able to preview data in the OLE DB Source Editor.
The data then goes through a Data Conversion transform. The OLE DB Destination editor uses a Native OLE DBSql Native Client connection manager. 'Test Connection' passes. The SQL Server edition is 2005 Enterprise x64.
When the package is executed within Visual Studio Professional on a Windows XP (sp2) machine, The package hangs at this dataflow. I set up a data viewer between the DB2 source and the conversion transform, but no data appears(DefaultBufferMaxRows is set to 100). In the 'Execution Results', validation completes, but nothing happens beyond that. There are no errors or messages in the error list. In the Control Flow, the dataflow control appears yellow, and stays that way.
I have noticed that when this package runs, a command window flashes up for a brief second.
How do I troubleshoot this? Why is there no data in the data viewer between the source and the data conversion transform?
Thanks for your help!
cdun2
Just purchased a new server, and am trying to install SQL Server 2005 standard, 64 bit. I'm running Windows Server 2005 r2 64 bit.
Setup support files installs fine.
System configuration passes all 14 checks.
Then it moves to install and just hangs there. Eventually, perhaps maybe after 20-30 minutes does it create a message saying that the Install has failed to respond. Retry or cancel.
Hi,
We have few installations of SQL Server express on Windows 2003 servers, platforms varrying from Celeron to P4.
Sql Server on system hangs after running for some time. Time varies from few hours to few days.
When we try to connect it just gives connection timeout error.
Any idea how to isolate the problem?
We would like to to enable more detail tracing but do not know how to do that.
Thanks in advance.
Has anyone seen this kind of error message? I pulled it from the SQL Server Logs. In this occurance the error occurs at 1:18am, my jobs begin to fail thereafter, and then the SQL Server Services auto restarts at 3:14am.
2003-01-24 01:18:00.15 server Error: 17882, Severity: 18, State: 1
2003-01-24 01:18:00.15 server Error accepting connection request via Net-Library 'SSNETLIB'. Execution continuing..
2003-01-24 01:18:00.15 server Error: 17059, Severity: 18, State: 0
2003-01-24 01:18:00.15 server Operating system error 1: Incorrect function...
I presume that the SQL Service and/or Server Agent becomes disfunctional, then the SQL Server Service restarts itself and everything is fine again. Once the SQL Server Service comes back up I get notifications that jobs failed, between the time from the error to the restart, and then I can restart the any failed jobs just fine. Also, during that time NetIQ does not think the SQL Service is down so it does not send out any Service outage notifications.
Another tid bit of info is that this server is dedicated to SQL Server 2000 Enterprise and has 5GB of memory with AWE enabled.
I think it could possibly be a memory leak but am not sure. Any help would be greatly appreciated. Thanks.
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.
thx
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!
View 2 Replies View RelatedWhen i will try to install SQL Server Express Manager in W2K SP4, its fail with error in Event Viewer:
View 1 Replies View RelatedHi
I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.
ANY HELP WOULD BE GREATLY APPRETIATED!!!
Thanks
Frank Srebot
I am experiencing extremely slow behavior (no activity for > 30 mins) during a SS 2005 Dev Edition install on XP SP2 on various steps during the SQL Server Database Services step - e.g. during "Setting File Security". This is very annoying.
I've found many postings on this issue indicating that OS calls in this part of the install (e.g. LookupAccountName()) are network speed dependent.
Does Microsoft have any plans to rework this part of the install to rid the need of these calls? I really wish the install would be possible without these calls - it's driving me crazy monitoring this for hours. We've got to roll SS2005 out to a couple hundred developers eventually and this isn't going to cut it.
Thanks for your time on this matter.
Best Regards,
Mike
Basic description:
We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services.
Our SQL Server box:
SQL Server 2000 Standard Edition SP4
Windows 2003 Server R2 SP1
Our Oracle box:
Test: Oracle 9.2.0.6
Production: Oracle 9.2.0.4
To setup the SQL box, I did the following:
1) Install Oracle Client Tools version 10.2.0.1
2) Restart Server
3) Modify the registry as follows:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll"
4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references
5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>'
6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>'
7) Changed the registry for MSDTC to match this:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTC]
"AllowOnlySecureRpcCalls"=dword:00000000
"FallbackToUnsecureRPCIfNecessary"=dword:00000001
"TurnOffRpcSecurity"=dword:00000001
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCSecurity]
"NetworkDtcAccess"=dword:00000001
"NetworkDtcAccessAdmin"=dword:00000001
"NetworkDtcAccessClients"=dword:00000001
"NetworkDtcAccessTransactions"=dword:00000001
"NetworkDtcAccessTip"=dword:00000001
"XaTransactions"=dword:00000001
"DomainControllerState"=dword:00000000
"AccountName"="NT Authority\NetworkService"
"NetworkDtcAccessOutbound"=dword:00000001
"NetworkDtcAccessInbound"=dword:00000001
8) Stopped and restarted services in the following order:
1) MSDTC Stop
2) SQL Server Stop
3) MSDTC Start
4) SQL Server Start
The stored procedure:
In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE')
SELECT column1,column2,column FROM SQLTable
This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes.
Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box):
SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE')
When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service.
Other queries that hang are:
SELECT * FROM SERVER..SCHEMA.TABLE
Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box:
The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll.
Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring.
Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query:
SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE
I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated.
Tim
Hi,
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 -
// get database handle
// get statement handle
//call sql bind parameter
// set memory for query string and copy it
// strcpy(sqlString,"{call pi_ml_*******(?, ?, 2, 0, 0, ?, ?, 100,'2007-05-04 19:37:37.357', '2007-05-04 19:37:37.357', 1, 1, 72, ?, 72, ?, ?, ?,0, 0, ?, 0, NULL, NULL, 0, 0, 0, 0, ?, '2007-05-04 19:37:37.357', ?, ?, ?, 0, 0, ?, ?, 0, NULL, ?, ?, 0, NULL, NULL,NULL, NULL, 0, NULL,NULL, 0, 0, 0)}");
//call sqlexecDirectA( for an ANSI build)
returnVal = SQLExecDirectA(hstmt, (UCHAR*)sqlString, SQL_NTS);
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.
Thanks,
Peeyush
I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.
I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?
Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.
Estimated execution plan attached, I can not get actual execution plan because query never completes.
I just bought a new laptop with Vista pre-installed and was able to install Visual Studio 2005 Pro, but when attempting to install SQL Server 2005 it just hangs at the IIS detection step. I was using the administrative account. I was able to install the Developer Edition of SQL Server 2000, but I need 2005.
Some posts mention IIS version issues and 32 vs 64 bit version issues. Can anyone ellaborate on what is happening and why? What is the work around?
After the installer has asked for authentication mode and has started verifying the authentication it simply hangs. I've let it sit there for an hour before taking the installer forcibly down but nothing seems to be happening except for two osql.exe processes showing up in the task manager.
I've tried both windows authentication and SQL server authentication but the outcome is always the same. At the same time I'm, however, able to login to the database engine and all services with management studio using both authentication modes.
The last few lines in HotFix.log are as follows:
05/09/2006 09:49:09.999 Authenticating user using Windows Authentication
05/09/2006 09:49:10.015 SQL Service MSSQLServer was previously running, ready for authentication
05/09/2006 09:49:10.218 SQL Agent Service SQLSERVERAGENT was previously running
05/09/2006 09:49:12.093 User authentication was successful
This would indicate that there's nothing wrong with the authentication but with whatever is supposed to take place after that.
There are quite a few lines in the log similar to the one below:
05/09/2006 09:47:08.438 Failed to read associated hotfix build information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBinnSQLServr.exe
Redist9_Hotfix_KB913090.log seems to be the only individual log file that ends with line saying "Hotfix package launched" whereas all other logs have several lines of checks being completed successfully.
Any ideas what might be causing the installer to hang and how to fix it?