Go To Page X Of Y Fast - Not Supported In Sql Server 2008?
Jun 21, 2007
I've been using full-text for quite some time, but I switched to another product for quite some time. The reason is that microsoft full-text doesn't offer the feature where one can get a resultset of page x out of y pages, i.e: give me top 100 matches of page 125 out of 5000 pages. I disappointedly waited for SQL Server 2005 and now it doesn't seem to support in sql server 2008 full-text search either. I had to get all 5000 pages of data, then jumped to page 125 to get just 100 records of that page--had to go get a coffee and came back for a 2+ millions rows table. Customers want the result in a split of a second.
Anyone knows if the final version of sql server 2008 will have better support for paging and getting top x rows? I scanned through 2008 BOL and didn't see any change from sql server 2005 or even sql server 2000 full-text search. (one improvement in sql server 2005 over sql server 2000 was the speed of populating large catalog and multiple languages support)
Thanks,
HH
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Code page translations are not supported for the text data type. From:1252 To: 950.I would like to know what this message means. I also installed thelanguage packs in advanced settings, Everything is set to English. Mywindows XP computer is XP English. For some reason I cant get an updateto go thru using ADO. like Recordset.UpdateAlthough other routines using .update workAny ideas?
View 6 Replies
View Related
Jun 12, 2015
I want to import xml file directly from web page into microsoft sql table. At the moment the import is done after the XML file is downloaded local.I want to skip this step to manualy download the file.It can be done in SQL? when i change the path i get this error: Cannot bulk load because the file URL... could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.)
below is the code
DECLARE @idoc INT
DECLARE @doc XML
SET @Doc = (SELECT * FROM OPENROWSET(BULK 'F:Folderbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- 1 LOCAL works
--SET @Doc = (SELECT * FROM OPENROWSET(BULK 'http://www.bnr.ro/nbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- from web i get error
SELECT @Doc
[code]...
View 0 Replies
View Related
May 11, 2015
I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?
View 6 Replies
View Related
Jul 28, 2015
I created a thread 2 days back on a performance problem RE non-trusted check constraints and foreign key constraints. We are planning to make them trusted to see if it works. On the other hand, we see about 50+ clustered/non-clustered indexes with >90% fragmentation but the page counts for all these indexes are in the range between 500-900. I'm reading in the whitepaper that index fragmentation with less than 1000 pages is not a concern.
View 7 Replies
View Related
Oct 16, 2015
I have moved some DB's from a physical server to a virtual server while the physical server is being upgraded. This morning when I checked to make sure all the jobs are running fine I saw that a backup job failed for one of the databases. The error was :
[color=#ff0000]Executed as user: NT AUTHORITYSYSTEM. BACKUP 'database 1' detected an error on page (1:1400911) in file 'D:Datadatabase 1.mdf'. [SQLSTATE 42000] (Error 3043) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.[/color]
Then I ran a DBCC check db command and found the following errors:
Msg 8928, Level 16, State 1, Line 1
Msg 8939, Level 16, State 98, Line 1
Msg 8976, Level 16, State 1, Line 1
Msg 8978, Level 16, State 1, Line 1
Msg 8939, Level 16, State 98, Line 1
Msg 8978, Level 16, State 1, Line 1
The messages varies from data from one page cannot be referenced on another, data page cannot be processed, page was not seen in the scan although the parent was etc etc.
View 9 Replies
View Related
Jul 16, 2015
We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.
When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.
Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.
View 1 Replies
View Related
Nov 19, 2015
I have a SSRS report with data in below format.
ID Name Date
1 A null
2 B 01/01/2012
3 C 01/02/2013
Also, I have a sort parameter @sort and values are (Name, ID, Date)
I want to apply page break whenever @sort=Name. There should be no page break when user selects @sort = ID or Date. Page break should happen only when @sort value = Name
it should be like this...
Page 1:
ID Name Date
1 A null
Page 2:
ID Name Date
2 B 01/01/2012
Page 3:
ID Name Date
3 C 01/02/2013
I need achieving the above task.
View 2 Replies
View Related
Apr 2, 2008
I'm trying to figure out how to determine why the same database is fast on my desktop but slow on a robust multiprocessor win2003 server.
I have a complex proc that takes about 27 seconds on my desktop sql server but takes almost 5 minutes on the server. The server processors are running at about 1% utilization while the proc is running.
Any ideas on how to troubleshoot this extremely poor performance?
View 8 Replies
View Related
Sep 21, 2006
Hi, I wanted to know that can we access a webpagefrom a store procedure in sql server 2000 like we run a exe file from sql server. Bye.
View 1 Replies
View Related
Mar 12, 2015
I have a report with tablix. when tablix returns no rows Footer is coming all the way up . How to display the footer at the bottom of the page all time.
View 0 Replies
View Related
Jan 5, 2006
On my development machine (XP Pro), when I run several reports in a loop, my connection count (in performance monitor) climb up to around 25 then immediately drop back to 1 when the reports are done being generated.
On our production server (WinServer 2003, IIS6) the SQL connection count climbs up to around the same number, then holds there for a few minutes. I know I am closing the connections properly in my code because they clean up fine on the development machine.
I'm sure this is a timeout setting or an IIS 6 option I need to tweak. How can I make my production machine remove those connections faster?
View 1 Replies
View Related
Feb 15, 2006
I have a very unusual situation - we converted a client from DB2 7.2 to MS SQL Server 2000, SP3. There is one report that runs very quickly when ran on the Database Server, but it takes a long time to complete when it is ran from a client system. This query is ran from within the application and not from within Query Analyzer.
Has anyone else here ever encountered this issue? What did it turn out to be? I am leaning away from it being a network issue.
Thanks in advance.
View 2 Replies
View Related
Sep 20, 2007
I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?
View 4 Replies
View Related
Mar 1, 2007
Storing large graph in
relational form doesn't allow us to perform graph operations such as
shortest path quite efficiently. I'm wondering if storing the graph as
objects would be better? How should I design the schema? Thanks!
View 3 Replies
View Related
Jan 11, 2002
I have 2 SQL 2000 servers. One has been added as a linked server.
They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain.
Thanks
View 1 Replies
View Related
Sep 28, 2005
The trick is to use a pivot tableCheck out the code herehttp://sqlservercode.blogspot.com/2...ops-in-sql.html
View 3 Replies
View Related
Sep 21, 2007
I have this 3rd party query:
SELECT D_P.PostId, D_P.TopicId, D_P.PostingUserId, D_P.BasePostId, D_P.ParentPostId, D_P.PostLevel, D_P.SortOrder, D_P.PostTitle,
D_P.PostDate, D_P.IsAnon, D_P.FileId, D_P.Property, D_P.IsDeleted, D_P.IsHTML, D_P.LastEdittedByUserId, D_P.LastEditDate,
CAST( ISNULL(D_P.FileId,0) AS BIT ) AS HasFile, F.FileName, CAST( ISNULL( U_RP.IsRead, 0 ) AS BIT) AS IsRead,
CAST( ISNULL( U_RP.IsFlagged, 0 ) AS BIT) AS IsFlagged, CASE WHEN IsAnon = 1 THEN CAST('Anonymous'AS VARCHAR(128))
ELSE U.FirstName+' '+U.LastName END AS Poster
FROM DISCUSSION_POSTS D_P
INNER JOIN USERS U ON D_P.PostingUserId = U.UserId
LEFT JOIN FILES F ON D_P.FileId=F.FileId
LEFT JOIN DISCUSSION_READPOSTS U_RP ON U_RP.UserId = 4265 AND D_P.TopicId = U_RP.TopicId
AND D_P.PostId = U_RP.PostId
WHERE D_P.TopicId = 460106
AND BasePostId IS NOT NULL
AND ((PostTitle LIKE '%flood%') OR (PostText LIKE '%flood%'))
AND ((PostTitle LIKE '%flood%') OR (PostText LIKE '%flood%')) -- No idea why they are doing this twice
ORDER BY PostDate DESC, D_P.PostId DESC
On the same server DatabaseA the query runs in less than 5 seconds. On DatabaseB the query times out. I can not see the execution plan for the query that times out. I am executing the query using SQL Server MGMT Studio. I have rebuilt all of the indexes, stats updated usage. Still no luck. I have checked all of the database setting and they are the same. If I comment out theste 2 lines "AND ((PostTitle LIKE '%flood%') OR (PostText LIKE '%flood%')) " the query run like it should and uses an execution plan all most the same as when using DatabaseA. Any pointers in the the right direction would be greatly apprecited!!!
Oh ya SQL Server EE 2005 sp 2 cu 3
Thanks,
~Joseph~
View 2 Replies
View Related
Mar 5, 2014
My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around 55% and now it is going to 70% and increasing.
Total OS memory is 32GB and I kept cap for sql server memory upto 29GB. Dont know what to do..
View 9 Replies
View Related
Nov 11, 2015
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies
View Related
Jun 27, 2006
Hello there.
I'm developing an eCommerce solutions based on the ASP.NET 2.0 Commerce Starter Kit, architechture. It uses the Provider Pattern. In my web-application, i use the CatalogProvider, to retrieve data from a SQL Server 2005 database. I call the methods through a handler class, whoch excists inside the WebApp. I also use a ShoppingCartProvider, OrdersProvider, ShippingProvider etc. in the same way.
In my Web.Config file, i have all the provers listed, and on each provider, the name of the connectionString to use are given.
My connection string looks like this:"connString" connectionString="Server=xxxx;Database=xxxx;Trusted_Connection=True;" providerName="System.Data.SqlClient" />
The problem is, that suddently, when browsing the website, that connects to the database through the providers, i get this error:
Keyword not supported: ',server'.
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.ArgumentException: Keyword not supported: ',server'.Source Error:
Line 31: public static IDataReader GetProductsByCategory(int categoryID)
Line 32: {
Line 33: return Commerce.Providers.CatalogProvider.Instance.GetProductsByCategory(categoryID);
Line 34: }
Line 35:
Source File: d:DevelopmentASPNETSeoShopApp_CodeHandlersCatalogManager.cs Line: 33 Stack Trace:
If i then go back to my web.config file, and removes the providerName section, of the connectionString, the website works again, for a short period. When the error return, i undo the deletion of the providerName, and it will work again... For a short time...
I've also tried to use another connectionsString, like this:Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI
But then the keyword which is not supported is: ', data source'
Does anyone know what the issue might be?
Thanks in advance...
View 9 Replies
View Related
Apr 12, 2004
Hello community!
Short question:
how can i retrieve all code pages that MSSQL server is installed with?
Thank you in advance for your help.
View 1 Replies
View Related
Jul 27, 2006
ahi all
iam new to VS.net 2005 and iam trying to convert ready made application from vb.net 2003 to vb.net 2005
and i cant find the sqlcedataadapter
its a windows CE application uses SQL Server CE
and i imported the system.data.sqlsrverce
and i added the sqlserverce refrence
may be its a new classes?
View 1 Replies
View Related
Aug 14, 2001
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Thank you!
View 2 Replies
View Related
Jul 28, 2015
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
“Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
View 1 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Aug 24, 2005
I have looked all over the MS site and can find very little information regarding SQL Server's support for the VMWare environment. Based on other information that I have obtained on other forums and web-site it looks like it is not supported by Microsoft, but I would like the company line if possible.
View 10 Replies
View Related
Nov 10, 2007
Hi All,
Can you please kindly let me know whether DAC(Dedicated Administration Connection) is supported in SQL Server 2005 Express Edition. As per the documentation available in MSDN(Trace Flags), Trace Flag
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express.
But when i tried to enable it by passing -T7806 as a parameter to SQL Server (SQL Express) service, the following information is logged into event viewer
Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
Also, the following error message is displayed when I tried to connect to DAC from command prompt
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
The version of SQL Server I'm using is
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6000: )
Can someone kindly me help me to enable DAC of SQL Express edition?
Many thanks in advance.
Gopoinath
Tech Thoughts
View 10 Replies
View Related
Nov 29, 2007
Books online "Deprecated Database Engine Features in SQL Server 2005" makes references to "Features not supported in Future SQL Server versions".
http://msdn2.microsoft.com/en-us/library/ms143729.aspx
Will there be an Upgrade Advisor available to help us identify issues with our code any time soon or should we start building our own?
View 3 Replies
View Related
Sep 13, 2007
A application using Sql Server 7 is developed under WinNT(Win2000, WinXp) . Now want to transfer this application into Vista OS. When install MSDEx86.exe, "SQL Server7 is not compatible with Vista" was warned. When do such kind of transfer, what kind of software related to Sql Server is required? And is it necessary to modify the code of application?
View 3 Replies
View Related
Apr 15, 2008
I was told that AES 128/SHA1 is supported for SQL Server Compact 3.5. The problem is that I couldn't find any product literature from Microsoft that specifies exactly that and my client wants us to provide proof on that.
I hope to get the endorsement from the forum here, and it would be great if someone could point me to some Microsoft resources that specifies the support clearly.
View 1 Replies
View Related
Oct 23, 2007
Hello All,
I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.
The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features.
From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing.
Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine.
One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported
It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.
Thanks in Advance,
Mitesh
View 4 Replies
View Related
