Thanks for the invitation to post a question, so I will post one.
I need to create a job step that uses cmdExec.
This is the command line I entering:
D:odbcTimeClockUpdatesinReleaseTimeKeepingNo nLogouts.exe
When I run this job the job fails. When I look at the job history, the only information I get is the date and time, user that ran the job and the fact that it failed. I haven't been able to get any CmdExec job to run at all. Can anyone tell me what I'm doing wrong?
Facts.
1. This exact same command is used by my network administrator using windows scheduler on the server. The only reason he wants me to create an sql server job is because it's mostly sql functions.
2. I know the Sql Server agent is running, because I have other jobs that are run.
3. I have verified that I have permission to run the file because I can go to the actual directory and run the exe.
4. Do I need to enclose my command in quotes i.e. “D:odbcTimeClockUpdatesinReleaseTimeKeepingN onLogouts.exe”
5. the path of the file I need to run is the path on the server and not the path on my local machine.
If you need any other information, please let me know
Thanks for you help
GEM
I want to convert .rdl to .rdc need full steps.Actually i created .rdl report using sp sucessfully.Now i want to convert it to rdlc while doing it iam getting some authentication error and some thing else.I created rdl in 2008 and i want to change it to rdlc 2010.
I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.
Here's the rub:
If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).
I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.
Any help is greatly appreciated.
I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)
I hope the answer is as simple as the question -- but after reading all the documentation I could find (understand?) and a lot of posts here, I'm no closer to achieving the goal.
I have a Visual C# app, DAYTRACKER, developed in VS2005. It uses a database with several tables constructed using SQL Server 2005 Developer Edition.
I want to deploy the app plus the database plus SQL Express to another machine, to be used by a single user (the administrator) with no need for network connectivity of any kind.
What I have so far is: 1. The application is successfully deployed from a CD-ROM, having used the Publish process within VS2005, and opens on the new machine -- without database connectivity, however. 2. SQL Express is successfully deployed (it deployed as a 'prerequisite' when I went through the Publish process in VS2005) 3. I manually copied the database's .mdf and .mdl files, using SQL Server Managers 'Copy Database' function, then transferred the copies to the new machine into the ..MSSQL.1MSSQLdata folder (where they appear along with the master.mdg, mastlog.ldf etc files)
Now, the DAYTRACKER application's DAYTRACKERConnectionString under 'Settings' in the VS2005 studio reads 'Data Source=DELL3;Initial Catalog=DayTracker;Integrated Security=True' (which are the appropriate parameters for the machine, DELL3, on which I wrote the program.)
The problem, of course, is that SQL Express on the new machine doesn't connect the application to the database. When I go to the 'SQL Server Configuration Manager' and go to the 'SQL Server 2005 Services' and double-click on the 'SQL Server (SQLEXPRESS)' icon (the service is running) and the user is logged on using 'Local System Account'. Under the 'Service' tab the Host Name is 'MUSIC' (which is the name of the new machine I've installed the app onto -- which of course is not the name - DELL3 - that the app's connection string is expecting). Under the 'Advanced' tab, I've tried correcting the name of the Startup Parameters default .mdf and .mdl entries to ..DayTracker.mdf and ..DayTracker_log.mdl, but the server won't start up after I make the changes.
What I'm hoping for: a step-by-step way of doing this type of deployment, preferable getting it all onto one CD-ROM, and installing it on the new machine so that it all works seamlessly from the start, not requiring any 'tweaking' of the SQLServer Express settings by the end-user.
But I'll take pretty much anything that fixes the specific db connectivity problem I've described.
Hi, I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory. Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.
Connecting to a networked SQL Server Box from my local machine
Open Query Analyzer from Start menu, logging in using sa account.
from the object browser i select my stored procedure (WEA_InsertClaim) - right click and select Debug.
i am prompted to enter the parameter values, which i do, auto rollback checkbox is checked - click Execute.
T-SQL Debugger opens and runs through the stored procedure.
but only buttons enabled are the "Go", "Toggle Breakpoint", "Clear All Breakpoints"
so i can set breakpoints etc. but when i select Go it will not stop at the breakpoints it just runs through the stored procedure from start to finish. giving the correct return code as its output
is there something i need to enable in order to make it stop at breakpoints??
Is there a good step by step guide to setting up an indirect configuration? I've followed the steps in Kirk Haselden's 'Keep your packages in the dark' article 3 or 4 times this afternoon and nothing seems to work. I cannot even get the XML configuration file to work by accessing it directly. I'm using the wizard to create the file.
Just wondering if there was something else out there....
I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Additional information: An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)
This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"
I have made the following configurations:
The user group (windows group) that the user belongs has the following roles in msdb :
i have made a proxy to sql server agent which has the following subsystems :
"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.
Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.
Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.
Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.
I have tried to make a DTS task running a batc-file on the same server as the SQL program is running using the CmdExec command.
My Command is as follows:
CmdExec serverpathatchname.bat
The problem is that everytime the step runs it report the following error:
"The system cannot find the file specified."
In the bat file I am also trying the de-map/map a network drive with another loginname/password than the one used on the SQL-server -> is this i problem?
Hope to hear from you soon, this is a bit urgent!!
Please provide tips to this adresse: qtip@bigfoot.com
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.
The step being executed by the Job is "Ren C:MyTestFile.csv C:MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.
I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."
As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.
I would appreciate any assistance anyone could provide. Thanks in advance!
I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:
select * from vProductProfitability
where year = 2003 and
MonthNumberOfYear = 1
The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.
I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?
BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.
Hi All, In creating 'steps' in JOBS, is it possible to execute many DOS CmdExec in one step, instead of creating several steps with a single DOS-cmd in each. For example:
If I created a job executing those 3 functions in 3 separate steps then it works fine. But if I put all those 3 DOS command in one step, it won't work. Somehow, SQL doesn't 'understand' it should execute after the end of each command OR I missed something here (apparently so!). I know if I put all those 3 DOS commands into a DOIT.BAT and execute it, it will work. But I want to use SQL Job to schedule it to run on a regular basis.
Anyone has run into this same problem? Thanks in advance. David Nguyen.
WE have a number of SQL 6.5 servers all of which run many scheduled tasks. However on one all tasks which use cmdexec to run batch files fail after 2-3 seconds. The history returns : 'No message' and the error logs just indicate that the tasks have faILED.
The identical tasks run on servers with what appears to be identical configurations and setup.
I would like to trap a return value from a cmdexec that is scheduled. The cmdexec returns 0 if it is a success and something other than 0 if it doesn't.
Can I raise an error from a command file. The command file calls a console application ( i.e. no interface ).
I have a Job that runs a cmdexec job step which executes a batch file in sql server 7.0 that runs fine In sql 2000 when i try to run that job it gives the following error and fails
---- the process could not be created for step 1 of job 0x677EF599B13FA743AA2D501D4C211AC4 (reason: The system cannot find the file specified). The step failed. ------ In fact i am not able to execute any cmdexec job in sql 2000 The owner of the above job is sa Does it have to do with SqlAgentCmdExec account which is set to corporate/administrator and has required permission.
I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now:
I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example:
And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits.
So, I changed the statement to point to dtexec.exe 32bits:
But executing this statement it does not even run. The errors are: 'C:Program' is not recognized as an internal or external command operable program or batch file
This was very wird to me, because this was very mentioned by the moderators.
So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source.
Now my questions are: 1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way?
2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101.
I want to copy a backup from the production server to another server periodically. The following is the DOS command and it works in DOS propmt d: is the local drive and g: is the mapping server --------------------------------------------------- "copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat"
I try to use the above command in the executive task with CmdExec as type, if failed. Then try on "cmd c/ copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat"
this time, it started to run with no error. But it run 40 miniutes and I cancelled it, since it should only run 10 minuts.
I'm a bit confused. On the command line of the job step property I entered dtexec /SQL... and got an error saying file not found, I assumed dtexec itself couldnt be found. So I tried /SQL .... by itself and got something that looked more like a security error. If I make the step property type "ssis" job appears to run fine, I receive my pkg's on success (rather than on failure) email but I know everything isnt fine because even if no data is ETL'd, first executable is supposed to (and always has in client) insert a row into an audit table and it doesnt. If I set job step "type" to t-sql and simply db email myself with t-sql command, everything is fine.
The first question is "Wouldnt dtexec need to be specified, how else could sqlagent know what I'm trying to run?" If answer is yes, what's wrong with my syntax or environment?
Can someone tell me how i can generate an exit code (Cmdexec). Ive tried running an .exe file in a job but when i do that the job hangs. When i check the history it says the exit code is somewhere in 27000. Now i'm wondering if it is possible for me to generate an exit code if the executable closes. PS. When i run the file from a batch file it works great
I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainNameSQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.
The job just copies files from one directory to another, here's the code.
D: cd MSSQLBACKUPAPAP_Primary xcopy *.* D:MSSQLBACKUPAP /s/y/d
When run as a job, with the owner of the job being DomainNameSQLAdmin the job fails with the following error message: Executed as user: DomainNameSQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
"Error executing extended stored procedure: Specified user can not login"
I have tried this through Enterprise Manager and get identical results, of course.
I have also tried all of the following: - different OS user accounts, including local system accounts with local admin rights; - assigning the OS account to a SQL login with System Admin role/rights; - specifically assigning the above SQL login with EXEC rights on the master.dbo.xp_CmdShell procedure; - verifying local security policy settings, as per the following link: http://support.microsoft.com/?id=283811; - pulling out my hair and banging my head against the wall.
I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig (on my dev machine).
Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.
However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:....myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig cannot be found.
What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.
Dear Experts, please guide me for replcation..... i'm using sql server 2005 server tools developer edition. my OS is professional 2000. and i've sqlserver 2000 client tools also
i've two databases in my machine. publisher is in different instance, and the transactions might be maximum 50 per day. my aim is when ever developer enters the data into the main database, automatically it should be updated on the my two databases also. i mean the server is sysA and the database is srtp. my machine is sys20 and the databases are srtp1, srtp2. how should i make sysA as publisher? i've right click on the server databases, but it is showing newsubscriptions option only.
Vinod Even you learn 1%, Learn it with 100% confidence.
Intel D975XBX €“ 930 €“ 2GB ram €“ HD 300GB - MatroxVD Windows XP Sp2 + updates Visual Studio 2005 Professional edition SQL Sever 2005 Standard Edition
20070203 Install Practice Files at D:Program FilesMicrosoft PressVisual C Sharp Step by Step.
Configuring SQL Sever Express Edition Hostname - xxxxxxxxx..
At sqlcmd €“s xxxxx...SQLExpress €“E got - Pipes error.
Open Microsoft SQL Sever 2005 - Configuration Tools €“ SQL Configuration Manager €“ select Protcols for SQLEXPRESS €“ select Named Pipes €“ enable €“ close.
At sqlcmd €“s xxxxx..SQLExpress €“E get Sqlcmd: €˜ €˜ : Unknown Option. Enter €˜ -? €˜ for help.
Chdir C:Documents and SettingsAll UsersStart MenuProgramsMicrosoft Server 2005 Sqlcmd €“s xxxxxx..SqlExpress €“E Hresult 0x2, Lvel 16, State 1 Named Pipes Provider: Couldnot open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
How do I fix this?
Need to be able to to use the Northwind Traders data base
I have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out. Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily. Currently this is all on the same machine. I have not moved it beyond where I am developing.
On the command line I am using the following command...
Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 6:59:40 PM Progress: 2006-08-16 18:59:41.29 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2006-08-16 18:59:41.29 Source: Data Flow Task Validating: 33% complete End Progress Progress: 2006-08-16 18:59:41.71 Source: Data Flow Task Validating: 66% complete End Progress Progress: 2006-08-16 18:59:41.73 Source: Data Flow Task Validating: 100% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 33% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 66% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 100% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.81 Source: Data Flow Task Pre-Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.84 Source: Data Flow Task Pre-Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.90 Source: Data Flow Task Pre-Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.90 Source: Data Flow Task Pre-Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Cleanup: 0% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 33% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 66% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 100% complete End Progress Progress: 2006-08-16 18:59:41.95 Source: Send Mail Task The SendMail task is initiated.: 0% complete End Progress Progress: 2006-08-16 18:59:42.09 Source: Send Mail Task The SendMail task is completed.: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 6:59:40 PM Finished: 6:59:42 PM Elapsed: 1.984 seconds
When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...
Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig". End Info Warning: 2006-08-16 18:40:03.15 Code: 0x80012012 Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-08-16 18:40:03.15 Code: 0x80012059 Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Info: 2006-08-16 18:40:03.20 ... Process Exit Code 1. The step failed.
When I try to install MS SQl server2000 on windows XP machine,it says the server component is not supported by OS.What should I do to get it run on my machine?
Hi, I'm Arash Baseri a Sql Server2000 developer and mail you from Dubai (U.A.E). I have a problem in Sql Server locking table. My problem is not reasonable so the more I researched the more I understand it is not my problem .it is a bug in Sql Server2000. Now I explain the situation: I have two tables (Table A and Table B).Table A has a clustered index on col1 and col2, Table B has a clustered index on col1 and col2. I join these tables and update col3 in table A, like this begin tran insert [Table A] select * from Arshiv_Master where Col1 between 26001 AND 26001
Update AI31 set Col3=Case when 1=1 then 1 Else 0 End From [Table A]AI5, [Table B]AI31 where AI5.Col1=AI31.Col1 And AI31.Col1 Between 26001 And 26001 /* intentionally I didn't rollback or commit transaction to hold locks on table*/ In another connection I execute this query and I face "Lock request timeout period exceeded" error message. set lock_timeout 1 set transaction isolation level read uncommitted
Update AI31 set Col3=Case when 1=1 then 0 Else 0 End From [Table A] AI5, [Table B] AI31 where AI5.Col1=AI31.Col1 and AI31.Col1 between 45018 And 60000 Now the most interesting part is here .when I use a smaller data range for Col1 no error message is shown. A query like this set lock_timeout 1 set transaction isolation level read uncommitted
Update AI31 set Col3=Case when 1=1 then 0 Else 0 End From [Table A] AI5, [Table B] AI31 where AI5.Col1=AI31.Col1 and AI31.Col1 between 45018 And 50000 As you see the difference between the last two queries is on WHERE clause especially on data rages of Col1. As you know Sql Server 2000 has row level lock and when I acquire a lock on a record the other records are free. So what's wrong with Sql Server that assumes the others records are locked. I tested this situation in Sql Server 2005 and this problem was not seen so I think it's a bug in Sql Server 2000.Who can help me about this problem?
Hi, I'm Arash Baseri a Sql Server2000 developer and mail you from Dubai (U.A.E). I have a problem in Sql Server locking table. My problem is not reasonable so the more I researched the more I understand it is not my problem .it is a bug in Sql Server2000. Now I explain the situation: I have two tables (Table A and Table B).Table A has a clustered index on col1 and col2, Table B has a clustered index on col1 and col2. I join these tables and update col3 in table A, like this begin tran insert [Table A] select * from Arshiv_Master where Col1 between 26001 AND 26001
Update AI31 set Col3=Case when 1=1 then 1 Else 0 End From [Table A]AI5, [Table B]AI31 where AI5.Col1=AI31.Col1 And AI31.Col1 Between 26001 And 26001 /* intentionally I didn't rollback or commit transaction to hold locks on table*/ In another connection I execute this query and I face "Lock request timeout period exceeded" error message. set lock_timeout 1 set transaction isolation level read uncommitted
Update AI31 set Col3=Case when 1=1 then 0 Else 0 End From [Table A] AI5, [Table B] AI31 where AI5.Col1=AI31.Col1 and AI31.Col1 between 45018 And 60000 Now the most interesting part is here .when I use a smaller data range for Col1 no error message is shown. A query like this set lock_timeout 1 set transaction isolation level read uncommitted
Update AI31 set Col3=Case when 1=1 then 0 Else 0 End From [Table A] AI5, [Table B] AI31 where AI5.Col1=AI31.Col1 and AI31.Col1 between 45018 And 50000 As you see the difference between the last two queries is on WHERE clause especially on data rages of Col1. As you know Sql Server 2000 has row level lock and when I acquire a lock on a record the other records are free. So what's wrong with Sql Server that assumes the others records are locked. I tested this situation in Sql Server 2005 and this problem was not seen so I think it's a bug in Sql Server 2000.Who can help me about this problem?
