I see the following message in SQL Server logs. What does this indicates. What should I do to avoid this.
2008-05-20 01:25:02.12 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 33920, committed (KB): 15142988, memory utilization: 0%.
The server configuration is
SQL 2005 Dev edition SP2 64bit
Win 2003 R2 SP2 Standard X64 editioin
RAM size is 16GB
In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
I frequently see the following message on SQL Server log
2008-06-09 07:46:18.17 spid3s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1079156, committed (KB): 17156388, memory utilization: 6%.
What does it indicates and what appropriate action has to be taken to fix it.
The database runs on
SQL 2005 Dev 64-bit SP2 9.00.3042.00 Win 2003 standard x64 SP2 16GB RAM
Can anybody shed some light on my issue? I'm reviewing my client's event log and finding the repetitive warning "A significant part of sql server process memory has been paged out. This may result in a performance degradation." This has been an ongoing issue that I've searched high and low to try and resolve.
The system is Windows Server 2003 Standard x64 Edition - SQL Server 2005 SP2 - 2.33 GHz - 4 GB Ram
We are running SQL 2005 SP2 x64 on Windows 2003 SP2 X64.
Server specs: Quad Core, each core runs at 2.33Ghz. 4GB memory OS and SQL on RAID 1 set Transaction log on RAID 1 set Databases on RAID 5 set
-The SQL service account is a domain account with the "lock pages in memory" rights. -The min memory use for sql server is 3000MBand the maximum is 2147483647MB.
For whatever reason, the sqlsrvr.exe process uses much less physical memory than the Virtual Memory (VM). When I look at the Task manager, I see 230MB for Mem Usage column and 300MB for VM size column under full workload. Why is the SQL server process being paged out? I have 3GB for it to use. Should I be concerned about the my SQL server?
I didn't configure the AWE as MS doesn't recomment using it on x64 bit systems. When I do "dbcc memorystatus", I get the following:
VM Reserved 4269536 VM Committed 196680 AWE Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0
I thought granting "lock pages in memory" rights to the sql service account automatically enables AWE in x64 bit version of SQL 2005 standard server.
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" this my sql script use [master]Go -- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO -- Switching to our databaseuse [DatabaseName]GO CREATE SCHEMA schemaname AUTHORIZATION usernameGO ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO /* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter' -- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter] -- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber] -- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
Hi AllSome my SQL Server are experience high memory usage.1. How can I detect which process which process cause the big memoryusage and not released?2. Which sql server components in this memory, and what are their usagedistribution?Any help will be appreciated.ThanksWillie
I got a Small Business Server 2003 running. It has 2 sqlserverprocesses. One of them is growing by 200mb every day. Does anyone havea clue to this. It's serving as a printserver, fileserver and exchangeserver. There is no specific use of the sqlserver. The antivirus isMCaffee
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
I am using a tool to monitor SQL Server and Windows. It is warning me that:
Process 1004:services has a virtual address space of 1,846.20 MB. This is close to the Windows two gigabyte address space limit.
When locate the process 1004, it shows 15 threads that Elapsed time for all of them is 1d, 3hrs. The Thread state is Waiting and the Thread Wait Reason is "Waiting for an Execution Delay to be resolved".
I think that 1d, 3hrs is from the time I rebooted my server.
Out techs informed me that they are getting reports of a system slow down. When they look, they find sqlserver.exe has lots of memory allocated to it. They reboot the server and then it runs okay for a few weeks. They tell me this just started happening recently.
SQLServer itself has not been touched in months. They are, however, starting to use one of the databases heavier.
I found a setting where you can set max_server_memory. Any problems if I set this to a value?
I have implemented the Stored Procedure "RowCount" method of controllingpaged recordsets in ASP as shown in this pagehttp://www.aspfaq.com/show.asp?id=2120.I have had to make heavy alterations to the code in order for it to workwith my application.It all worked fine until I tried to sort my data by a field which containeda NULL value (even in just one record). Before I added additional criteriafor the Unique_ID field the stored procedure returned zero records whensorting by such a field. I then added the Unique_ID criteria to the ORDER BYand WHERE clauses (as seen below) and it excludes records.In short obviously what I want this to do is order by fields that containNULL values (e.g. fld4 in sample data below). It currently pages and ordersperfectly in fields which have data in every row.If anyone reads this and helps me, thanks *very* much in advance.(P.S. sorry about the horrible state of my code but I've been messing withit trying to get this fixed for ages)(P.P.S the reason for passing in the long SQL string as a parameter in theSP is because the string is generated using complicated loops and I foundthis much easier in ASP than doing it in SQL)******************************************Here is an example of a call to the procedure from ASP:EXEC st_paging_rowcount 7, 1, 50, 'SET ROWCOUNT 50 SELECT utbl7.*,utbl6_1.fld1 AS fld3_Name FROM utbl7 LEFT OUTER JOIN utbl6 utbl6_1 ONutbl7.fld3 = utbl6_1.Unique_ID WHERE utbl7.fld5 >= @fld1val ORutbl7.Unique_ID >= @uniqueid ORDER BY utbl7.fld5',5******************************************Here is the SQL SP:CREATE PROCEDURE st_paging_rowcount@tableid INT,@pagenum INT = 1,@perpage INT = 50,@finalselect NVARCHAR(1500),@sortfield INTASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INT,@querystring1 NVARCHAR(200),@querystring2 NVARCHAR(200),@querystring3 NVARCHAR(200)SELECT @querystring1 = N'SELECT @rows = COUNT(*),@pages = COUNT(*) / @perpageFROM utbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)'--SELECT GOGOGO = @querystring1EXEC sp_executesql@querystring1,N'@rows INT OUTPUT, @pages INT OUTPUT, @perpage INT',@rows OUTPUT, @pages OUTPUT, @perpageIF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum < 1 SET @pagenum = 1IF @pagenum > @pages SET @pagenum = @pagesSET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,TotalPages = @pages,TotalRows = @rows-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE @fld1val NVARCHAR(64)DECLARE @uniqueid INTSELECT @querystring2 = N'SET ROWCOUNT ' + CAST(@lbound AS NVARCHAR(15))+ 'SELECT@fld1val = fld' + CAST(@sortfield AS NVARCHAR(15)) + ', @uniqueid= Unique_IDFROMutbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)ORDER BYfld' + CAST(@sortfield AS NVARCHAR(15)) +', Unique_ID 'SELECT test0 = @querystring2EXEC sp_executesql@querystring2,N'@fld1val NVARCHAR(64) OUTPUT, @uniqueid INT OUTPUT',@fld1val OUTPUT, @uniqueid OUTPUT--SELECT test = @finalselectEXEC sp_executesql@finalselect,N'@fld1val NVARCHAR(64), @uniqueid INT',@fld1val, @uniqueidSET ROWCOUNT 0END*********************************************Here is some sample data from the table (e.g. would like to sort by fld4):Unique_ID Date_Added Who_Added Locked fld1fld2 fld3 fld4----------- --------------------------- ----------- ----------- ------------------------------------------------------------3 2005-09-16 16:12:30.200 1 0 SmithJohn 1 NULL4 2005-09-16 16:12:41.013 1 0 JonesChris 1 NULL6 2005-09-16 16:13:10.187 1 0 StamovStilian 1 NULL7 2005-09-16 16:19:15.437 1 0 LewickiSteve 1 Colchester8 2005-09-16 16:19:36.937 1 0 JamesPhil 1 NULL9 2005-09-16 16:20:35.327 1 0 LeroyDidier 1 NULL
Hi, I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet. The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows. When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message. aspnet_wp.exe was recycled because memory consumption exceeded the 306 MB (60 percent of available RAM) How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues. -Thanks
I have worked in other ETL tools. So, i am trying to figure out how to do thefile decryption and process the data in memory using SSIS.I am using SSIS on Azure VM and my source files are on Azure storage. The files are encrypted and we are trying to use Phython script to decrypt the files and pass it to SSIS. I found out that Execute Process task can call the Phython script. However, i would like to get the decrypted data from the file and pass it to the next task (control flow) in SSIS without saving it as a file (in-memory). I found that execute process task output can be stored as a Standard Output Variable or to an object. Will this work or do I need to follow any other methods (since we need the entire file to be sent for additional processing).
Details:MS SQL 2000 dual Intel 1.2 GHz processors.1 GB RAM2.1 GB dBDynamic Memory Managment.No other apps running on this server.First question:Since I have Dynamic Memory Managment setup, Is it usuall that thesqlsrv.exe process on the server steadily climbs and is in the 800 to900 MB range. There is only about 20 MB free. In theory this is howDMM can work, but do people really see it work this way.Second question:I had users complaining about lockups in the app I have to supportthat connects to this dB. At first I thought it was the large use ofmemory, but once I was able to see in Enterprise Manager that therewas process blocking several other processes. EM then locked up and Icouldn't get to the details of what the exact process was that wasdoing the blocking. After restarting SQL services things were fine.When I checked the logs there was nothing there about a hung process.The logs seemed very sparse. Why would there not be anything in thelogs about it. The logs actually seem very thin on any information.Thanks,T.
When I try to install MsSQL Server 2005 Develop Edition do I get the error:
[Microsoft][SQL Native Client]Shared Memory Provider: No process is on the other end of the pipe.
I have trying to look at other posts on this forum and elsewhere, but cant find any solution that works for me - mainly cuz all solutions is after the installing.
Before trying to install MsSQL Server 2005 Dev did I install VS.Net 2005 Pro. First did the Native Client make troubles, but got it to work with reinstalling it, but now does the SQL setup stop on every try with the error above.
I have tried looking if the MSSQLServer is running when it tries to connect during install, and everything says it is running (Services, Net start, Taskman.).
I dont run any special setup on my system - it is a normal Windows XP Pro SP2 with all updates. I just need the SQL server installed so I can develop locally without access to out main SQL server.
I have been using MsSQL 2000 before and never had any problems, but the 2005 keep on bugging me.
The only solution I havent tried is to reinstall Windows itself, but I will pref. not to do so.
And to be honest, then have I no idea what a "pipe" is - I am used to develop webapplications and not so much on server maintaince/troubleshooting.
I did a load testing and found the following observations:
1. The Memory:Pages/sec was crossing the limit beyond 20.
2. The Target Server Memory was always greater than Total Server Memory
Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?
I am getting the following error when i try to connect to the my web site using froma different server. A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) i am using sql express and i attach the database through the connection string in the web config. Any ideas
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?
Is there a way, other than simply changing the format of the SRS report, to create an old fashond Paged style report - similar to the ones you can get from Crystal Reports?
What is the recommended mechanism for selecting paged results from SQL.
Presently I pass various params including the request Max Items Per Page and the requested page.
The I execute the query as a count with the search params.
Then comes the paging logic, which validates the page number against the request page and number of hits etc.
Then a temp table and record variables are created for the results.
Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).
Then I return the temp table & some additional return params with the Total Hits etc.
The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.
I have a paging query that uses OpenQuery to access the MSIDXS indexing service and return records where text is matched. The query looks like this:
select top 25 * from OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC') as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst and catname_cst='Main' and (v.docid_cin not in (select top 600 v.docid_cin from OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC') as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst order by v.docid_cin)) order by v.docid_cin
Not the exact query, but it is just as slow as the query in my program. This query will retrieve the top 25 records starting at record 601 where 'report' is found in a document and where the category = 'Main'
What is really weird about this is that in management studio this query will sometimes return in like 3 seconds, while the exact same query will take 30 seconds using ASP.Net / System.Data.SqlClient (and I mean it actually sits on sqlCommand.ExecuteReader() for a long period of time). Also, does management studio cache the table randomly, because sometimes it'll take forever and other times it'll be fast.
I do not have response issues with this paging query in non-MSIDXS queries.. I can for example return the 5,000th - 5,025th of 50,000 records in less than a second while the OpenQuery manages to get progressively slower very quickly (like 3 extra seconds per page of 25 records, so it takes like 20 seconds to return 150-175 of 600).
I am trying to move my application (asp.net) from a non-paged select to a paged query.
I am having a problem. Below is my Stored Procedure. The first Query in the procedure works...the rest (which are commented out ALL work interactively, but fail when the program tries to access....The ONLY THING I change is the stored procedure I switch the comment lines to the non-paged procedure and it works, I try to use the any of the paged procedures and it fails with the same error (included below)
I can't see where any of the queries are returning different results. I have also included the program abort that happens, it is the same for all of the paged queries.
ALTER PROCEDURE dbo.puse_equipment_GetAllTypedEquipment ( @EquipmentTypeId int, @StartRowIndex int, @MaximumRows int ) AS
-- ************************************************************************************************ -- Non-Paged OUTPUT -- THIS WORKS!!!!! SET NOCOUNT ON SELECT Equipment.*, EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId where Equipment.EquipmentTypeId = @EquipmentTypeId AND Equipment.IsDeleted = 0
/* -- ************************************************************************************************ -- Using a Temp Table --THIS WORKS INTERACTIVELY, But NOT When Called by the program SET NOCOUNT ON create table #PagedEquipment ( IndexId int IDENTITY(1,1) Not NULL, EquipId int )
-- Insert the rows from Equipment into the PagedEquipment table Insert INTO #PagedEquipment (EquipId) select EquipmentId From Equipment WHERE IsDeleted = 0
SELECT #PagedEquipment.IndexId, *,EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN #PagedEquipment with (nolock) on Equipment.EquipmentId = #PagedEquipment.EquipId INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId Where #PagedEquipment.IndexId Between (@StartRowIndex) AND (@StartRowIndex + @MaximumRows +1) */
/* -- ********************************************************************************************** --Using the With to create a temp table (in memory)..works interactively but fails when --called by the application.. --THIS WORKS INTERACTIVELY, But NOT When Called by the program Set NOCOUNT ON; With PagedEquipment AS ( SELECT EquipmentId, ROW_NUMBER() OVER (Order by Equipment.EquipmentId) AS RowNumber FROM Equipment WHERE EquipmentTypeId = @EquipmentTypeId AND IsDeleted = 0 )
SELECT RowNumber, Equipment.*, EquipmentCategory.Name as Category, EquipmentType.Name as EquipmentType FROM PagedEquipment INNER JOIN Equipment ON Equipment.EquipmentId = PagedEquipment.EquipmentId INNER JOIN EquipmentCategory ON Equipment.CategoryId = EquipmentCategory.CategoryId INNER JOIN EquipmentType ON Equipment.EquipmentTypeId = EquipmentType.EquipmentTypeId WHERE PagedEquipment.RowNumber Between (@StartRowIndex+1) AND (@StartRowIndex+1+@MaximumRows) return -- ******************************************************************************************* */
/* -- ******************************************************************************************** --nested selects --THIS WORKS INTERACTIVELY, BUT NOT WHEN CALLED FROM THE PROGRAM SET NOCOUNT ON Select * From ( Select Row_Number() OVER (Order By Equipment.EquipmentId) as RowNumber, Equipment.*, EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId where Equipment.EquipmentTypeId = @EquipmentTypeId AND Equipment.IsDeleted = 0 ) equip Where equip.RowNumber between (@StartRowIndex+1) AND (@StartRowIndex + 1 + @MaximumRows ) -- ************************************************************************************************ */
Server Error in '/pUse' Application. --------------------------------------------------------------------------------
Arithmetic overflow error converting expression to data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int.
Source Error:
Line 148: { Line 149: List<EquipmentDetails> equipment = new List<EquipmentDetails>(); Line 150: while (reader.Read()) Line 151: equipment.Add(GetEquipmentFromReader(reader)); Line 152: return equipment;
Source File: c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs Line: 150
Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlDataReader.HasMoreRows() +150 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212 System.Data.SqlClient.SqlDataReader.Read() +9 PredominantUse.DAL.Equipment.EquipmentProvider.GetEquipmentCollectionFromReader(IDataReader reader) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs:150 PredominantUse.DAL.Equipment.SqlClient.SqlEquipmentProvider.GetTypedEquipmentList(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentSqlClientSqlEquipmentProvider.cs:103 PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:259 PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:238
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Hi, I am trying to implement filtering on a custome paged stored Procedure, here is my curent Stored Procedure which doesn't error on complie or run but returns no records. Anyone got any ideas on how to make this work???<Stored Procedure>set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: Peter Annandale-- Create date: 22/10/2006-- Description: Get Filtered Names-- =============================================ALTER PROCEDURE [dbo].[proc_NAMEFilterPaged] -- Add the parameters for the stored procedure here @startRowIndex int, @maximumRows int, @columnName varchar(20), @filterValue varchar(20)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;SELECT CODE, LAST_NAME, Name, TYPE, NUMBER FROM (SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER, ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum FROM dbo.NAME n LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED WHERE @columnName LIKE @filterValue ) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1END </Stored Procedure> Any assistance would be greatly appreciated.. Regards..Peter.
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?
Hello 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.
Dear 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
How 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".
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".
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.
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:
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.
I'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...)
