Classic Paged Report
Oct 6, 2007Is 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?
View 6 Replies
ADVERTISEMENT
Memory Being Paged Out.
Jan 29, 2008
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
Thanks in advance for any help!
Paged Results From SQL Query?
Jan 25, 2006I 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?
View 4 Replies View RelatedProcess Memory Has Been Paged Out
Jun 9, 2008Hi all
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
Thanks.
Paged Result Sets
Aug 28, 2006What 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.
Thanks for any input,
Martin.
ASP Classic And SQL 2005?
May 14, 2007I want to know if it is possible to connect from classic ASP to a SQL 2005 database, and if so, can someone provide a sample connection string, I'm not sure if it is my connection string or server config. Thanks, Ed.
View 5 Replies View RelatedSQL Everywhere, Ntext && ADO Classic
Jun 24, 2006Hello!
I'm trying to move from MSDE to SQL Everywhere in my project, which requires using of ADO classic and i have some problems storing ntext data in SQLEv database using parametrized command.
Could it be that the problem is because of ADO classic? Is it ok to use old ADO with SQLEv OLEDB provider and will compatibility with old ADO be maintained in SQLEv?
Below is JScript code illustrating the situation:
-------------------------------
var oConn = new ActiveXObject("ADODB.Connection");
oConn.Open(sConnStr);
var oCmd = new ActiveXObject("ADODB.Command");
oCmd.ActiveConnection = oConn;
var sText = "test text";
oConn.Execute("create table tblTest(sText ntext)");
oCmd.CommandText = "insert into tblTest(sText) values(?)";
oCmd.Parameters(0).Value = sText;
oCmd.Parameters(0).Size = sText.length;
oCmd.Execute();
-------------------------------
It gives me 'Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider: The given type name was unrecognized. [,,,,,]'.
If i remove 'oCmd.Parameters(0).Size = sText.length;' error changes to 'Insufficient memory to complete the operation.'
The same code works ok for MSDE and SQLExpress.
OS:Windowx XP SP2
MDAC version: 2.8
Many thanks!
MSIDXS OpenQuery, Paged, Very Slow
Jun 12, 2008I 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).
Paged Query Not Working Via Program
Mar 27, 2008I 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
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +371
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Adapters.ControlAdapter.CreateChildControls() +12
System.Web.UI.Control.EnsureChildControls() +128
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
SQL Server Paged Recordset Problem
Sep 19, 2005I 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
View 3 Replies View RelatedSQLserver (sqlsrvr.exe) Process Is Being Paged Out!
Jun 15, 2007We 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.
Thanks
Sql Server Process Memory Has Been Paged Out
May 20, 2008
Hi All
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
Thanks.
Connecting To SQLExpress From Classic ASP.
Nov 16, 2005Migrating a site to a new server. Moved data to SQLExpress. Changed connection string to point to SQLExpress and now I am getting this error.
Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database "PHCSQL" requested by the login. The login failed.
/func/inc_DatabaseFunctions.inc, line 5 Do I need to do permissions differently than MSDE?Did connection strings change?Help?
Connecting To Sqlexpress From Classic Asp
Apr 28, 2006hi,i'm using classic asp to try and connect to a sqlexpress database on a development server. i get the following error:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/dbtest.asp, line 8
I'm using the following script which runs fine against a regular SQL server (version 8) on the network.
<%@LANGUAGE="JAVASCRIPT"%>
<%
var strCon, conn, sql;
strCon = "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=rapidHB;User Id=rapid;Password=xxx";
conn=Server.CreateObject("ADODB.Connection");
conn.Open(strCon);
sql = "SELECT product_code FROM products WHERE product_type = 1";
var results= conn.Execute(sql).GetString();
Response.write(unescape(results));
%>
I have tried changing Data Source to servernameSQLEXPRESS, changing initial catalog to master, using a user name defined on the database and changing the provider to SQLNCLI but nothing has worked.
Anyone got any idea what I'm doing wrong? Using ASP.Net is not an option.
Rgds,
lukemack
Filtering A Custom Paged Stored Procedure
Oct 22, 2006Hi, 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.
View 1 Replies View RelatedSQL 2012 :: Server Process Memory Has Been Paged Out
Apr 1, 2015In 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%.
Connecting To SQL Server Express Via ASP (classic)
Aug 31, 2007Hi All,
I'm having trouble getting at SQL Server 2005 Express via ASP code.
I'm using the following connection string in my ASP code:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn = "Provider = SQLNCLI;" & _
"Data Source = .SQLEXPRESS;" & _
"Initial Catalog = myDB;" & _
"User ID = DB_reader;" & _
"Password = a#koddkobn5;"
I keep getting the error:
Microsoft SQL Native Client (0x80004005)
Login failed for user 'DB_reader'. The user is not associated with a trusted SQL Server connection
I have made sure that the user exists in SQLEXPRESS and that server Security is set to SQL Server and Windows Authentication mode.
Can anyone think of anything I'm missing or that I should try?
We've lost our SQL Server admin and I'm trying to develop locally using Express while we hunt for a new admin.
Thanks so much for any assistance you have time to offer.
Very best,
Thom Cox
How Can I Filter A Date Range In Classic ASP?
Feb 21, 2008I have a Classic ASP page that provides me a view on Orders posted by customer for a selected month and year from a SQL Server 2000 database. This ASP page has a Stored Procedure that returns the orders posted by month and year. However, my needs are to be able to display the view by month, day and year to month, day and year, For example January 15, 2008 to February 14, 2008.
The current Classic ASP page has a dropdown to select the month and year from this dropdown only displays months and years for the months and years the customer had posted orders.
What I’d like to know is how to add to the classic asp page the means to input a date range on the fly that would return the report by month, day, year to month, day, year.
Classic Datespan Membership Query.. Help?
Apr 26, 2008Classic problem..
Say I have two simple tables (one with members the other with status
changes):
CREATE TABLE [dbo].[member](
[memkey] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mname] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dob] [datetime] NULL,
[effective] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[memberstatuschange](
[memkey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[transtype] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY]
sorry missing referential for simple example.
select m.memkey,m.effective,s.transtype, s.date from member m left
join memberstatuschange s
on m.memkey = s.memkey
produces:
001 2006-11-01 00:00:00.000 1 2007-12-01 00:00:00.000
001 2006-11-01 00:00:00.000 2 2007-02-02 00:00:00.000
001 2006-11-01 00:00:00.000 1 2007-02-05 00:00:00.000
002 2006-11-02 00:00:00.000 1 2007-06-01 00:00:00.000
003 2006-11-03 00:00:00.000 1 2007-07-01 00:00:00.000
003 2006-11-03 00:00:00.000 2 2007-08-02 00:00:00.000
004 2006-01-01 00:00:00.000 NULL NULL
005 2007-01-01 00:00:00.000 NULL NULL
006 2007-02-05 00:00:00.000 NULL NULL
transtype 1 = stop, 2 = restart.
effective date marks the start the original start of membership. So
in the case of memkey 001 he started on 2006-11-01, stopped on
2007-12-01, restarted on 2007-02-02 and stopped again on 2/5/2007.
memkeys 004,005 and 006 don't have NO status changes.
presuming that we should never have a status code of restart without a
stop but that the data may have violations of that.
How can I query my tables to return every date of ACTIVE participation
during two dates?
So for example if I say give participations criterias of 11/7/2006
and 2/7/2007, it should pull two records for memkey 001 as follows
memkey, days, start, stop
001, 6, 11/1/2006, 11/7/2006
001, 2, 2/5/2007, 2/7/2007
Thank you for any help or information!!.
Connecting To SQL Server 2005 From Classic ASP
Aug 3, 2006I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.
The ASP code looks like this: strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = strConnect
conn.Open
Set objCommand = Server.CreateObject("ADODB.Command")
Set objRecordset = Server.CreateObject("ADODB.Recordset")
Set objRs = Server.CreateObject("ADODB.Recordset")
With objCommand
.ActiveConnection = conn
.CommandText = "sp_GetShowList"
.CommandType = adCmdStoredProc
If Len(strBeginDate) And IsDate(strBeginDate) Then
.Parameters.Append .CreateParameter("@i_DateStart",adDate,adParamInput,,strBeginDate)
If Len(strEndDate) And IsDate(strEndDate) Then
.Parameters.Append .CreateParameter("@i_DateEnd",adDate,adParamInput,,strEndDate)
End If
End If
End With
Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs.NextRecordset
arrSched = objRs.GetRows()
The error message below occurs at the 2nd GetRows() command. Operation is not allowed when the object is closed.
Can anyone tell me what I might be doing wrong?
-Dan
Accessing 64-bit MS SQL Server 2005 From Classic ASP
Aug 20, 2006I'm considering shifting my database server to 64-bit MS SQL Server 2005 for improved scalability and performance. I'm concerned, however, that my classic ASP website (which sits on a separate server) may have problems communicating via ADO/OLEDB because of communication problems between 32-bit IIS on the web server and 64-bit MS SQL Server on the database server.
My current set up (which works fine) is:
Web Server: Windows Server 2003, Standard Edition, SP1 - running IIS with a set of ASP websites
Database Server: Windows 2000 SP4, running MS SQL Server 2000
Connection String:
MyConnection="Provider=SQLOLEDB;Network Library=DBMSSOCN;SERVER=192.168.0.1;INITIAL CATALOG=MyDatabase;UID=MyUserID;PWD=MyPassword"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open MyConnection
My core question is:
If I change my database server to new machine with 64-bit Windows Server 2003 running 64-bit MS SQL Server 2005, will my (32-bit) web server be able to connect from ASP as it does now?
Thanks for your help!
Jed
A Significant Part Of Sql Server Process Memory Has Been Paged Out
Jul 26, 2007On 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%%.
From Asp Classic && Asp.net To Access Database Changing To Sql Server
Dec 17, 2006Hello, We are re-writing our site in asp.net using sql server. Most of the site uses asp classic and it was to an access database. During the conversion we have everything working correct to SQL Server except for the asp.net connection string. There is an important part of the application using asp.net which works fine with our connection string to the access database. To recap our problem is the connection string from asp.net to sql server. This code works fine for the asp.net to access in the web.cnfg file </microsoft.web> <connectionStrings> <add name="SalesConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb" providerName="System.Data.OleDb"/> <add name="ODBCSalesConnectionString" connectionString="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb"/> <add name="RawConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb" providerName="System.Data.OleDb"/> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|inventoryStatus.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> The code in the global.asa which works fine for classic asp to sql server also works fine and is as follows '--Project Data Connection Application("sales_ConnectionString") = "Driver={SQL Native Client};Server=DMSERVER01;Database=SQLsalesMain;UID=nTrack;PWD=nTrack2k3" Application("sales_ConnectionTimeout") = 15 Application("sales_CommandTimeout") = 30 Application("sales_CursorLocation") = 3 Application("sales_RuntimeUserName") = "" Application("sales_RuntimePassword") = "" Our programmer who set this up is out for a couple of weeks and I would appreciate any help in the correct connection string from asp.net to the sql server database in the web.cnfg file Thanks
View 5 Replies View RelatedLimitation On Conn.Execute In Classic .ASP && ADO With SQL Server
Aug 13, 2004Hello,
I have a project (using classic ASP & SQL Server) which adds one execute sql statement at a time to a temporary array, and then I join that array with a chr(30) (record separator), to a string variable called strSQL. I then run the following line of code:
conn.execute(strSQL)
I was wondering if there was any limitation to how large the strSQL variable can be? Reason I ask is because thru log writes I can see all of my sql execute lines exist in the variable strSQL prior to running the "conn.execute(strSQL)" command; however, not all of the lines run at the time of execution. Remember, this bug only is occuring whenever I have say over 600 sql lines to execute.
My understanding is that there was no limitation on the size of the string strSQL; however, in the interest of getting the bug fixed quick enough, I decided to just run a loop for each sql statment and run "conn.execute(strSQL)" every 50 times. This, in turn, has solved the problem and I do save all of my data; however, my original bug still exists.
Does anyone know why I have to split the sql commands and execute.com every 50 times instead of just being able to do it once ?
Please let me know. Thanks in advance.
Classic ASP Errors Out Only In Production Environment With EOF Error
Oct 1, 2007
Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'EOF'
Used the same connection string and DSN (i.e pointing to the same database/server) in both environments. ASP works in Test Environment. Fails in Production Environment.
Test and Production have same versions of software.
Environment Details:
OS: Windows 2003 SP2
MDAC: 2.8
Database: HP Neoview
Any help appreciated.
Thanks,
Venkata.
Connection String For Classic ASP Global.asa To SQL Server 2005
Dec 1, 2005I migrated my SQL Server 7 database to the new SQL Server 2005 that I installed on my PC. I have classic ASP programs on my PC that used to access the SQL Server 7 database. However, global.asa and these ASP programs can no longer connect to the new SS 2005 database.
View 9 Replies View RelatedClassic Sales Summary With Royality Logic Has Me Stumped.
Nov 1, 2007 Hello everyone. I'm stuck with what must be a common sql challenge.
I've got this single table that looks like this:
Store, Original Store, Product, Sale type, Method, Amount
There will be two product types (prodA and prodB)
There will be two Sale Types (New and Recharge)
There will be two Method (Cash and Credit)
The tricky part is I also need to report sales activity when a store
was the Original Store for a customer Recharge at another Store.
The report needs to look like this:
Store , Product, Cash New Total, Cash Recharge Total, Credit New
Total, Credit Recharge Total, Outside Recharge Total
Grouping/Totally on Store/Product.
In the above "Outside Recharges" means the Store was the Original store
and another store performed a recharge on it's customer.
Thanks for any help or information
Migrating SqlServer2000-&&>2005 And Classic ADO: Problem With AddNew To Recordset, Related To AdFldUnknownUpdatable Attribute?
Mar 26, 2006The application is running ADO (MDAC) version 2.81.1117.0.
The application gets an recordset created by SQLServer by an select statement. In this case the recordset is empty.
The application adds a record to the recordset by AddNew(). Works fine. But when assigning the first field (smalldatetime in database) I get ADO error -2147217887.
I have inspected the recordset and I found a difference between the working one from SQLServer2000 and the new one from SQLServer2005: The Attributes item for the fields in the recordset has the flag adFldUnknownUpdatable set in the working SQL2000 version but is not set from SQL2005.
The database on SQL2005 is copied from the SQL2000 system and attatched to the SQL2005 system. I Have checket that the compability flag for the database on the SQL2005 system is set to SQL2000 compability.
Nils Nordenbrink
Winkonsult AB
Sweden
Removing Individual Results From A Paged Set Of Results.
Oct 19, 2007Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
This Feature Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Report
Jun 16, 2006SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
Reporting Services :: Add Sub-report To Main Report Using Report Builder?
Sep 9, 2015I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
View 5 Replies View RelatedModifying A Report Created In Report Builder In The Report Designer.
Jun 30, 2006After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?
The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.
Any suggestions would be appreciated. Thanks
How Do I Jump To Another Report Based On A Value In My Current Report? Report Has No Parameters.
May 3, 2007How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies View Related