Does A Background Process Block Sp_dboption Command
Jan 25, 2008Does an sp_dboption 'dbname','single user',true command execute succesfully when a background process is working on a database.
View 3 Replies
ADVERTISEMENT
Process Block Itself
Jul 3, 2007Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
End A Blocking Process (Was Block)
Feb 11, 2008Ho do I clear a kill process blocking others to run
View 3 Replies View RelatedLOG: Process ID 59:0 Owns Resources That Are Block
Apr 11, 2007Process ID 59:0 owns resources that are blocking processes on Scheduler 2
I am getting tons of this into sql server log second day in a row (2000 sp4)
Noticed heavy usage prior
What is causing it? Solution?
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Want To Block All Other Db Activity While My Archive Process Is Running...
Oct 29, 2007i'd like to block all other processes that interact with my database while my archive process is running on that database. Can this be done?
Disable Or Block TRUNCATE Command
May 2, 2008
Hi guys, am I know is there any way for disabling/blocking the truncate command so that won't accidentally truncate all the tables within the db in a production server ?
T-SQL (SS2K8) :: Why Block Scope Variables Exist Outside Of Block
Dec 3, 2014I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
Not Enough Storage To Process This Command
May 26, 2008Hi
My name is Atma SIngh
I am have a strange problem here, on my system.
I have develop a solution that runs on window xpsp2 and Microsoft SQL Server 2005 Express Edition with Advanced Services SP2.
Once in every day the system will prompt a error message "Not enough storage to process this command".
The Hard Disk space is avaliable.
I will never get this error in the lab test.
So I am forwarding this error to the forum if someone there knows how to resolve this problem.
Thanks
Atma Singh
atma-singh@hotmail.com
SQL Command To Process An Access Macro?
Jul 20, 2005Here's the scenario. Using a campaigning applicaion for aneNewsletter. It writes to an Access table. I have an ASP web page thatwrites to another table. I chained 3 Access queries together into aMacro to copy that data to the campaigning table, give a numericidentifier to that record, and then delete all records with thatidentifier (in the 2nd table). This will keep the ASP > Access tableclean.The campaigning software allows for a pre-processing SQL command tobe run. I would like to automate the updating of the table byinitiating this Access Macro from the SQL command. Anyone have anyideas on the command/syntax?Thanks in advanceJasonJoin Bytes!
View 1 Replies View RelatedProcess Exit Code Of Successful Command
Jul 5, 2000I wish to have a batch file exit with a return code of 3 if it fails.
In the batch file I have the single command: Exit 3
On SQL Server the Process Exit Code of a successful command is set to 0.
Yet, when I run the job SQL Server says that it is successful.
Best Method For Using FTP Command Xp_cmdshell Or Execute Process Task
Dec 8, 2005Hi, I need to send a table data into flat and then ftp into different location.
I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error.
Could you advice me regrding network engineer thought and any solution for avoiding this error.
---------------------------
Execute Process Task: C:WINDOWSsystem32ftp.exe
---------------------------
CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0.
---------------------------
Thanks,
Execute Command Process - Remote Batch File?
Feb 23, 2006Good afternoon-
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:
cd C:Trandev
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:Trandev represents the remote environment
I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.
SSIS:
I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.
Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP
Sp_dboption??
Apr 20, 2001this is the message i get when i use 'sp_renamedb....."
A member of the sysadmin role must set database 'distributors' to single user mode with sp_dboption before it can be renamed.
what do i need to do set database to single user mode (i believe that that is what i'm need to do now?
Sp_dboption
Jul 25, 2005I noticed that BOL says that the above is possibly going away. What's the official way to print out all your db options once it goes away, i.e. what are we supposed to do to quickly look at dboptions with doing sp_dboption 'databasename'?
View 6 Replies View RelatedSp_dboption And Sp_configure
Apr 14, 20081.could any one tell me the difference between sp_dboption and sp_configure.
View 1 Replies View RelatedSp_dboption Parameter
Feb 28, 2008Hi there,
I'm trying to remember which parameter i used for sp_dboption to fix the "data access" problem when I tried to synchronize one server to another.
I can't remember the exact error msg but it was something like "data access is not permitted" or "data access denied" or something like that.
does anyone know what error msg I am talking about? and how to fix it using sp_dboption??
I'd very appreciate it if someone can give me the exact error msg as well!
thanks,
Integration Services :: Illegal Characters In Command Line On Execute Process Task Editor
Aug 26, 2015I'm trying to execute a simple VBS file from the Executable command line in the Execute Process Task Editor.
My line is this : cscript.exe "c:convertcsvssisXlsToCsv.vbs"
SSIS keeps saying there are illegal characters here. I've Googled and looked about 20 articles and I can't resolve it.
I have a ForEach that loops through Excel files and changes them to CSV files using code i found. This script takes an original Excel file and transfers it to a new CSV file in a new directory.
So in DOS at the CMD line I would type : XlsTocsv.vbs originalfile.xls newfile.csv
I have the original file and new file in the Arguments line so I'm assuming that after the script executes it will look at the filepaths in the loop and loop through them so I want it do to this when it runs:
XlsTocsv.vbs [User::@ExcelFile] [User::@CSVFile]
I just can't get it to execute and I keep getting illegal characters.
Follow Up To Exec..master And Sp_dboption
Jul 20, 2005Hi allI had some info on setting db owners and chaining so I can execute a dtsrun(many thanks to Dan for that)I have taken the comments on board and done some more research on theinternet, but I'm still struggling.I am testing this on my personal edition, but when I runEXEC sp_dboption 'my dbname', 'db chaining', trueit tells me that the SP doesn't accept the option db chaining.I ran select @@version and I have SP3 on where I believe this option wasimplentedcan anyone give me any further pointers.many thanks for any helpAndy
View 2 Replies View RelatedTransaction (Process ID 135) Was Deadlocked On Lock Resources With Another Process And Has Been Chosen As The Deadlock Victim.
Nov 14, 2007
Hi,
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
cherriesh
FCB::Open: Operating System Error 32(The Process Cannot Access The File Because It Is Being Used By Another Process.) Occurred W
Dec 3, 2007Hello all,
I am running into an interesting scenario on my desktop. I'm running developer edition on Windows XP Professional (9.00.3042.00 SP2 Developer Edition). OS is autopatched via corporate policy and I saw some patches go in last week. This machine is also a hand-me-down so I don't have a clean install of the databases on the machine but I am local admin.
So, starting last week after a forced remote reboot (also a policy) I noticed a few of the databases didn't start back up. I chalked it up to the hard shutdown and went along my merry way. Friday however I know I shut my machine down nicely and this morning when I booted up, I was in the same state I was last Wenesday. 7 of the 18 databases on my machine came up with
FCB:pen: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf'. Diagnose and correct the operating system error, and retry the operation.
and it also logs
FCB:pen failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
I've caught references to the auto close feature being a possible culprit, no dice as the databases in question are set to False. Recovery mode varies on the databases from Simple to Full. If I cycle the SQL Server service, whatever transient issue it was having with those files is gone.
As much as I'd love to disable the virus scanner, network security would not be amused. The data and log files appear to have the same permissions as unaffected database files. Nothing's set to read only or archive as I've caught on other forums as possible gremlins. I have sufficient disk space and the databases are set for unrestricted growth.
Any thoughts on what I could look at? If it was everything coming up in RECOVERY_PENDING it's make more sense to me than a hit or miss type of thing I'm experiencing now.
[Execute Process Task] Error:The Process Exit Code Was -1 While The Expected Was 0.
Mar 11, 2008Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server
What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%
However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".
There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log
Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
thanks in advance
Dave
Execute Process Task Error - The Process Exit Code Was 1 While The Expected Was 0.
Jan 30, 2007How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.
Thanks,
Monisha
Execute Process Task - Error :The Process Exit Code Was 2 While The Expected Was 0.
Mar 20, 2008
I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.
For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.
I am using the Execute Process Task and the process parameters I am providing are:
WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat
The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"
the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.
The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :
[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]
Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.
Can anyone help ?
Integration Services :: Dataload Process - Error Capturing Process
Aug 20, 2014I'm pulling data from Oracle db and load into MS-SQL 2008.For my data type checks during the data load process, what are options to ensure that the data being processed wouldn't fail. such that I can verify first in-hand with the target type of data and then if its valid format load it into destination table else mark it with error flag and push into errors table... All this at the row level.One way I can think of is to load into a staging table then get the source & destination table -column data types, compare them and proceed.
should I just try loading the data directly and if it fails try trouble shooting(which could be a difficult task as I wouldn't know what caused error...)
Transaction (Process ID 66) Was Deadlocked On Lock Resources With Another Process.
Feb 14, 2007Hi Folks,
I am having this table locking issue that I need to start paying attention to as its getting more frequent.
The problem is that the data in the tables is live finance data that needs to be changed and viewed almost real time so what I have picked up so far is that using 'table Hints' may not be a good idea.
I have a guy at work telling me that introducing a data access layer is the only way to solve this, I am not convinced but havnt enough knowledge to back my own feeling up. (asp system not .net).
Thanks in advance
Transaction (Process ID 65) Was Deadlocked On Lock Resources With Another Process
Jan 6, 2012We are facing deadlock issue in our web application. The below message is coming:
> Session ID: pwdagc55bdps0q45q0j4ux55
> Location: xxx.xxx.xxx.xxx
> Error in: http://xxx.xxx.xxx.xxx:xxxx/Manhatta...Bar=&Mode=Edit
> Notes:
> Parameters:
> __EVENTTARGET:
> __EVENTARGUMENT:
[code].....
ASPNETDB.MDF: The Process Cannot Access The File Because It Is Being Used By Another Process
Feb 17, 2007Hi,
I'm trying to upload the ASPNETDB.MDF file to a hosting server via FTP, and everytime when it was uploaded half way(40% or 50%)
I would get an error message saying:
"550 ASPNETDB.MDF: The process cannot access the file because it is being used by another process"
and then the upload failed.
I'm using SQL Express. Does anybody know what's the cause?
Thanks a lot
Cannot Process Request Because The Process (3880) Has Exited.
Nov 15, 2007
Hi. When I try to start a package manually clicking the Start Debugging button I get this after a little while:
Cannot process request because the process (3880) has exited. (Microsoft.DataTransformationServices.VsIntegration)
How can I prevent this from happening? This happens every time I want to start the package and
every time the process id is different. Here it is 3880.
Darek
Background Processes
Jan 24, 2008Hi all,
Can anybody tell me what are the background processes running in sqlserver databases.
regards
Susheel
Background Jobs
May 20, 2008Hi,
I am a bit hazy on what exactly are background jobs with respect to SQL. Can someone provide or point me to an example which will help me return some results for the view "sys.dm_exec_background_job_queue".
I understand that because background jobs may be short lived it is not quite easy to view or capture them.
Any help will be appreciated.
Thanks,
Karun
Set Background Color Using RGB Value
Jan 11, 2007Hi All,
i have a field which stores the related RGB values for each record
I need to set programatically the background color for some objects in the report using the RGB value stored for the record.
how can i convert the rgb value to color and then set the background color for the report object. ?
Please Help
Thanks In Advance
sud
Text As Background
Oct 4, 2007Hi,
I would like to know how can I put some static text in the background of my report.
I thought that it should be as easy as placing textbox, fillling it with text value and setting ZIndex. Everything is OK in ReportViewer control and when I save rendered report as PDF file (text is behind other fields, for example behind table). But when I save my report in Excel format (*.xls) text is placed below other objects, not behind them as it should be.
Any suggestions?
Regards
Stanley
Background Replication
May 31, 2006
We are developing a embedded system based on Windows CE 5 and SQL Server CE 3 in C++ where we need to do merge replication in background while the front end application still need to perform operations on the database. A solution would be to run the merge replication on a separate thread while the application thread still can access the database to do inserts and updates. I have been researching if it is possible to access the database with queries during a merge replication, but I can not find an answere. Does anyone have experience of this and generally know if it is possible to implement with SQL Server CE 3 in the native API?
Best regards
Johan Johansson