Scan Servers To See Who Is Running SQL Server
Apr 26, 2006Does anyone have a script which will scan all the servers on my network to see where SQL Server is running?
View 1 Replies
ADVERTISEMENT
What Is The Difference Between Clustered Index Scan And Table Scan
Jan 29, 2006How can I improve performance of my search if I am looking in a table with more than ten million rows with a "like query"?
Does putting an index mean only telling the computer to start in a particular order?
if I index all the coloums does my search get faster ?
how can I decide on an indexing strategy?
Running Batch Scripts On Other Servers
Jul 1, 1999I want to keep applications off of my database server so I have set up an application server (APPServer1). On APPServer1 I have a batch file that bcp’s data from DBServer1 into DBServer2 and is being passed the server name of DBServer2. On DBServer1 I have mapped a drive to the directory of APPServer1 and have created a task to run the batch job and pass the server name. So here’s my problem: when the scheduler runs the job, the bcp to DBServer2 fails, because it can not find DBServer2. When I execute the exact same command line in a DOS Box on DBServer1, the bcp works fine. I have verified that the server name is being passed correctly to the batch job in both methods.
Thanks in advance.
George
Running Two Servers W/SQL 2000 - Ideas? Help?`
Feb 22, 2005I had a website that ran with one server that had SQL 2000 as the DB. I have upgraded to two webservers with MS SQL 2000 on both machines and a load balancer in front of the two servers to handle sending requests to the server with the least load.
My problem is, I am not sure how to start to work my second server with the MS SQL. I need to make sure both DB's are exact duplicates of each other so if a user hits one server they are pulling up the same info as another user hits the other server.
I have a lot of queries running on the ASP pages I use, several pages use search forms for many various processes, and I have several pages that are for users to input information that is to be stored and sent out via email to all the other members.
Not by any means am I a SQL guru of any kind, I am lucky I know what it is
So, can anyone point me in the right direction as to what I should look for or into to work the best possible process to keep these DB's mirrored?
Now these servers are on their own private network using a second NIC card, I did this so the SQL can talk between the servers without using the public network bandwidth.
How do I keep these SQL DB's duplicated?
What are some great suggestions?
Have you done this before? if so, what has worked best for you? Is it hard to do for someone with limited DB experience? (I learn really quick if I know what I need to learn)
At this point I am confused as to what the best practice would be. Help?
Running Multiple Db Servers For One Db (import/exp
Feb 29, 2008Hi,
I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.
Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.
I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.
I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?
I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.
Thanks very much,
mike123
How To Retrive The Names Of Sql Servers Running In A Network Through An Vb6 Application
Apr 3, 2007
I want to retrive the names of sql servers running in a network through an vb6 application .
I found a API which list out the SQL server names in the network , but I am able to connect only to the main
SQL server in the network and for other instances it gives error " DBNETLIB] Connection Open () SQL server doesn¡¯t exist or access denied"
Can anyone help me?
Thanks
Goldie
Running SSIS On Different Servers Without Changing Connection Information
Nov 14, 2007What is the best way to run SSIS scripts on different servers without changing connection information. Our test server is ppntt140 and our production server is ppntd110. If I create a script on server ppntt140 what can I do so I can move it to server ppntd110 without changing any connection information? Database names are the same, it is just the server that changes. What is the best way to handle this? Thanks in advance.
View 4 Replies View RelatedDatabase Access By Machines Running SQL Servers 2005 32-bit And 64-bit
Oct 11, 2006Hello
We have 1 machine running SQL Server 2005 x64 (64 bit). The other machine is backing this SQL Server up. It is a 32-bit machine, so it requires (?) a 32-bit version of SQL Server 2005.
Would this back-up machine work correctly on a database previously managed by a 64-bit machine and vice versa.
Thank you!
Data Synchronization Accross Multiple Servers Running Replication
Apr 19, 2007Hello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
Index Seek, Index Scan, Table Scan
Oct 4, 2007
Hey,
what is the difference between Table Scan und Index Scan?
I find no difitions in the internet
Finchen
What Is Table Scan, Index Scan And Index Seek??
Sep 21, 2007Hi,
I want to know wht is a
TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????
Problem In Using Linked-servers While Running MSSQLService Under Low-priv. User Account
Apr 24, 2007Hi.
While hardening a ms-sql2000 , I faced with a problem and I`m completely lost !
few days of reading and google searchs didn't gave me any hint...
Here's the scenario :
Ms-sql is connected to Oracle , through "MS OLE DB provider for Oracle" .
By default MS-SQL runs as SYSTEM , but even if we change it to a "local admin"
account , everything works fine .
The problem is that it's not wise to let sqlservice to run under privilaged accounts such
as system or a member of 'local administrators' . So I tried a normal local user on the
host running sql . I fixed every related problem appearing because of using a limited user
account and ms-sql works fine in all aspects but one !
While using normal-user account , sql-server fails to load linked-servers and this error
pops up in enterprise-manager :
"OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error."
I've tried much to find root of this error ( including any comments from related KB articles... ) but no luck . My guess is that , using OLE requires administrative privileges on host , and as I'm running SqlService with normal user, it fails to use OLE. So I should give requried permissions to the user running SqlService . But the problem is that I've no idea where/how I should do that. I've already tried some registry/file permissions but non of them helped me.
Some where I red that using ODBC instead of OLE may help , but that seems fail too !
*Note that I'm almost sure it`s a problem OUT of circle of ms-sql , meaning any modifications should apply OUT of ms-sql , because simply giving local administrative privileges to the user, fix the problem.
Any comments?
regards
Hamid.K
Problem In Using Linked-servers While Running MSSQLService Under Low-priv. User Account
Apr 28, 2007Hi ,
I've asked about my problem previously in "sql server security" forum ,but
no hint . so I've been redirected here .
here's my problem :
http://forums.microsoft.com/msdn/showpost.aspx?postid=1513189&SiteID=1
IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.
Sep 20, 2006Hi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.
Scan Document Data To SQL Server
Jul 20, 2005Does anyone know of any Windows software to scan the same paper document andenter the results to an SQL Server database?We have a Photocopier machine that can automatically scan several documentsat a time and email them to an email address. We'd like to scan in anassessment form that rates a person from 1-10 on punctuality, appearance,efficiency, willingness and professionalism, and then submit the values toan SQL 2000 database.Thanks in advance for any suggestions.Dan Williams.
View 2 Replies View RelatedRemote Scan On Linked Server - Fast SELECT/Slow UPDATE
Aug 14, 2001In 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!
SQL Server 2014 :: Memory-optimized Queries Using Table Scan Instead Of Seek?
Sep 19, 2015I've been having some trouble getting a single-column "varchar(5)" field to reliably use a table seek instead of a table scan. The production table in this case contains 25 million rows. As impressive as it is to scan 25 million rows in 35 seconds, the query should run much faster.
Here's a partial table description:
CREATE TABLE [dbo].[Summaries_MO]
(
[SummaryId] [int] IDENTITY(1,1) NOT NULL,
[zipcode] [char](5) COLLATE Latin1_General_100_BIN2 NOT NULL,
[Golf] [bit] NULL,
[Homeowner] [bit] NULL,
[Code] .....
Typically, this table is accessed with a query that includes:
SELECT ...
FROM SummaryTable
WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)
This query insists on using a table scan. I've tried WITH (FORCESEEK) for example, but that just makes the query fail.
As I've investigated this issue I also tried:
SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')
When I run this query with 64 or fewer (actual, valid) ZIP codes, the query uses a table seek.But when I give it 65 or more ZIP codes it uses a table scan.
To summarize, the production query always uses a table scan, and when I specify 65 or more ZIP codes the query also uses a table scan. I'm wondering if the data type of the indexed column (Latin1_General_100_BIN2) is somehow the problem. I'll likely try converting the ZIP codes to an integer to see what happens.
Scan
Oct 8, 2007
I write a work about database, and I want to write the diffrence between Table scan and Index scan. When is the index scan effectient? Use the database a Table scan when there is an index on the table or always a index scan when the index is nonselective? Can be a Table Scan effecient than a index scan?
Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help
Nov 21, 2007I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
Scan Density < 50%
Aug 14, 2001I am having problems w/ the indexes on a particular table. Currently the scan density if 25%. I have ran the dbcc dbreindex and the scan density does not improve. I have manually dropped and recreated all of the indexes on the table (there are only two) and still no help.
I have a clustered Primary Key index on my part_number and project_number fields and another index on the project_number field. If I drop the second index and only have my primary key index, it still doesn't help. The fill factor was originally set to 90% and I changed this to 30%, 20%, 10%, 0% and the best scan density I could acheive was 50%.
The data in this table is not updated that often, on average about 15 records a day are updated, and there are only 107 records. This is a small table, but there is a possibility for it to grow rapidly and I want to be prepared now.
Does anyone have any ideas or suggestions? I've tried everything I can think of with no luck.
TIA,
Chris
How To Get Rid Of Table Scan
May 26, 2000Can anyone tell me how to get rid of the Table Scan(1 million rows)being performed on the
The last line, option (loop loin) stopped table scanning the B.ss_manifest and started using the index, I'd like both tables to use the index.
This is the argument I get from execution plan under the table scan.
Object ((D4000).(dbo).(shipstop)as (A))
update drivers set dr_miles_run = case when D1.miles > 0 then d1.miles else 0 end
from (select mf_dr_nbr, sum( case when A.ss_end_dt < '05/17/00' then
( cast((datediff(day, '05/17/00' , B.ss_end_dt ) + 1) as float) /
cast( (datediff(day, A.ss_end_dt , B.ss_end_dt ) + 1) as float) * mf_ld_miles)
else mf_ld_miles end) as miles
from manifest, shipstop A, shipstop B
where mf_manifest_nbr = A.ss_manifest_nbr
and mf_manifest_nbr = B.ss_manifest_nbr
and A.ss_stop_type in ('OR','SA')
and B.ss_stop_type in ('DT','RD')
and ((A.ss_end_dt >= '05/17/00 00:00' and A.ss_end_dt < '05/24/00 00:00')
OR ((B.ss_end_dt >= '05/17/00 00:00' and B.ss_end_dt < '05/24/00 00:00'))) and mf_status > 3 group by mf_dr_nbr ) as D1
where Drivers.dr_driver_nbr = D1.mf_dr_nbr
option (loop join)
Thanks for any ideas!
Count Scan
Aug 21, 2006Hi,
I have a simple temp table which looks like this:
CREATE TABLE #t7e07c01fa80143ff84cb14a2307809f7
(
[AUTOID_TEMP] [int] IDENTITY (1, 1) NOT NULL,
...
)
ON [PRIMARY]
ALTER TABLE #t7e07c01fa80143ff84cb14a2307809f7 ADD
CONSTRAINT [PK_t7e07c01fa80143ff84cb14a2307809f7] PRIMARY KEY NONCLUSTERED
(
[AUTOID_TEMP]
)
ON [PRIMARY]
Insert Into #t7e07c01fa80143ff84cb14a2307809f7
Selet columns... from t....
-- get total records
Declare @TotalCount as int
select @TotalCount = count(AUTOID_TEMP) from #t7e07c01fa80143ff84cb14a2307809f7
Now the above last statement does a index scan. I am new to indexing/tuning and was wondering if its normal - if so why and can I somehow enhance this?
Thanks
Scan Directory
Nov 1, 2007Hey guys,
I have a procedure that runs everyday which takes the IIS log file from the previous day and imports it and calcs values. I would like to change this from taking the previous day to scanning all the file names in the folder and comparing them to a database to see if they have already been scanned before. I already have a table called DatesScanned which lists the filename and scandate.
IE:
D:Logsfile1.log
D:Logsfile2.log
D:Logsfile3.log
D:Logsfile4.log
The 4 file names will be scanned and compared to DatesScanned to see which has been imported already.
Can anyone point me to a resource or information on how I would get the directory listing of a folder so I could compare it to the DatesScanned table.
Thanks a bunch!
Jeff
Scan Count
Jul 23, 2005Hi,Can someone please explain the following1. Meaning of scan count as reported when "statistics io" is turned onprior to running a query.2. In which situations could you have an identical database runningon two diferent servers , with identical database serverconfigurations, running identical queries, with identical query plan,report large discrepency in the scan count . This is one of thepossibilities we are looking at in terms of the reason why one serverruns the job in 12 hours and the other in 24 hours.Server 1--------Table 'TRANS_HISTORY'. Scan count 216, logical reads 897093, physicalreads 44, read-ahead reads 900599.SQL Server Execution Times:CPU time = 27766 ms, elapsed time = 46850 ms.UNIT_NUMBERACCOUNT_TYPETRANSACTION_TYPEServer 2--------Table 'TRANS_HISTORY'. Scan count 491, logical reads 952759, physicalreads 51, read-ahead reads 954414.SQL Server Execution Times:CPU time = 31563 ms, elapsed time = 145595 ms.UNIT_NUMBERACCOUNT_TYPETRANSACTION_TYPEI thank you in advance for your assistance.Puvendran
View 1 Replies View RelatedTable Scan
Jul 9, 2007Does SQL Server allow a table scan to be used when querying a table that has a clustered-index? If yes, could someone please show me the syntax? I have tried with (index(0)) but this appararently means a clustered index scan when there is a clustered index on the table to be queried.
Or does clustered index scan mean the same thing as a table scan when the table has a clustered index? Confused.
Thanks
Table Scan
Nov 28, 2007Friends,
I am using this query in a table with 29,000 records having non-clustered index on emp_id
select * from employee
where emp_id between 100 and 1000
and gender = 'M'
order by emp_id
execution plan shows a table scan in table employee. Any reason?
select * from employee with (index = emp_id)
where emp_id between 100 and 1000
order by emp_id
ofcourse this uses index seek
regards,
Priw
Invalid Log Scan
May 21, 2007
I do not understand it but by connection to my database seems to keep crapping out. I get the below error and my app appears to be unable to access the connection. Then a couple restarts later it tells me that my login under my Windows User failed . The database is there and besides maybe a couple records longer there isnt anything corrupt about the data, and in fact, if I restart the entire PC most times it will allow me to connect again. I can even still access the data tables through the C# editor. Any idea why this error is being thrown?
Thx
Code SnippetSystem.Data.SqlClient.SqlException was unhandled
Message="A system assertion check has failed. Check the SQL Server error log for details
The log scan number (35:460:2) passed to log scan in database 'F:\PDLOGSHEET\PDLOGSHEET\BIN\DEBUG\DATABASE1.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'F:\PDLOGSHEET\PDLOGSHEET\BIN\DEBUG\DATABASE1.MDF' (database ID 26) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Cannot open user default database. Login failed.
Login failed for user 'GC\cbcottier'.
Location: logscan.cpp:1925Expression: UtilDbccIsInsideDbcc ()SPID: 51Process ID: 3640
Location: logscan.cpp:1925Expression: UtilDbccIsInsideDbcc ()SPID: 51Process ID: 3640"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=20
LineNumber=65536
Number=3624
Procedure=""
Server="\\.\pipe\E74940B1-0BD7-42\tsql\query"
State=1
StackTrace:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PDLogSheet.Database1DataSetTableAdapters.ReportTitlesTableAdapter.Fill(ReportTitlesDataTable dataTable) in F:PDLogSheetPDLogSheetDatabase1DataSet.Designer.cs:line 4362
at PDLogSheet.frmLogSheet.frmLogSheet_Load(Object sender, EventArgs e) in F:PDLogSheetPDLogSheetfrmLogSheet.cs:line 42
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at PDLogSheet.Program.Main() in F:PDLogSheetPDLogSheetProgram.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Tabel Scan
Oct 29, 2007Hi All,
When an end user view a report using report manager or a custom build web site, would query of the report scan the tables in original data base? In this case it is a OLTP data base.
Thanks
Development Servers, Auto-update Live Servers
Aug 21, 2001can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
Linking SQL 2005 Servers To SQL 2000 Servers Problems
Sep 27, 2007I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
SUM Uses Table Scan But Not Index
Jan 16, 2002Hi I'm issuing a SELECT on a field with the SUM on SQL Server 7. I have an index on the field in the WHERE clause but upon analysis, the Query Optimizer always uses a Full Table Scan. Can anyone explain why and is there a way to use the index.
HEre's the structure:
SELECT SUM(colA)
FROM TABLE tblB
GROUP BY colC
An index exists on column colC.
Thanks
How To Impove Scan Density
Aug 27, 2001Case
I run dbcc showcontig
Result
DBCC SHOWCONTIG scanning 'rpRateCode' table...
Table: 'rpRateCode' (213627854); index ID: 1, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2] !!!!!!!
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3088.5
- Avg. Page Density (full).....................: 61.84%
Bellow is table structure
CREATE TABLE [dbo].[rpRateCode] (
[ID_RateCode] [ID] NOT NULL ,
[ID_ScheduleOfPresence] [ID] NULL ,
[ID_ScheduleOfBilling] [ID] NULL ,
[IsExtensible] [bit] NOT NULL ,
[IsFixedRate] [Boolean] NOT NULL ,
[ID_RateCodeFollow] [ID] NULL ,
[ID_RateDef] [ID] NULL ,
[ID_MarketSegment] [ID] NOT NULL ,
[ID_HskLevelPattern] [ID] NULL ,
[ComplimentaryType] [AType] NULL ,
[BillingType] [AType] NULL ,
[Discriminator] [Discriminator] NOT NULL ,
[PrintRateOnConfirm] [Boolean] NOT NULL ,
[PrintRateOnArrivalList] [Boolean] NOT NULL ,
[PrintRateOnRegCard] [Boolean] NOT NULL ,
[MinNights] [NbOfNights] NULL ,
[MaxNights] [NbOfNights] NULL ,
[IsLimited] [Boolean] NULL ,
[presencetype] [tinyint] NULL ,
[RateNote] [varchar] (60) NULL ,
[IsAgeCatDiffRoom] [Boolean] NULL ,
[ID_Currency] [ID] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[rpRateCode] WITH NOCHECK ADD
CONSTRAINT [PK__rpRateCode__2EBAFAC0] PRIMARY KEY CLUSTERED
(
[ID_RateCode]
) ON [PRIMARY]
GO
My question how to increase Scan Density for this table if CLUSTERED index exist?
Index Seek Vs Scan
Nov 4, 2002I have created a nolock view off a table to prevent locks. I have users coming in through MS Access that have switched their queries to run against the views. Now we are noticing that queries that used to run as a clustered index seek against the table are running as a clustered index scan against the table and performance in the queries has dropped.
Is there any way that the same query that hits the view instead of the table can be made to run faster or at least use the index seek?
Thanks,
Steve