'_WA_' Named Indexes

Sep 27, 1999

I have rows in sysindexes where the name starts with '_WA_Sys_' followed by a column name from my application tables. What is causing the creation of these sysindexes rows?

View 2 Replies


ADVERTISEMENT

How To Delete '_WA_' Indexes?

Oct 14, 1999

Now that we understand what these WA indexes are, how do we get rid of them so they don't show up in sysindexes? We have disabled the auto create/update stats options so we won't continue to generate them.

View 1 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

Sql Command Named : Contains

Mar 13, 2008

I am looking at some SQL in a stored proc and I noticed that theyuse a command called "contains". Can someone tell me more about this command. I can't find anything on it in the SQl books I own?
SELECT distinct  dmz.fileName,  dmz.title,  dmz.description,  dmz.fileType,  createdBy,  createDateFROM Documents dmzWHERE d.pId like @PIDAND  (contains(dmz.title, @SearchWords)  or  contains(dmz.description, @SearchWords)  or  contains(dmz.filename, @SearchWords)  or  contains(dmz.createdBy, @SearchWords)  )

View 2 Replies View Related

Named Pipes Vs Tcp/ip

Oct 20, 1999

I am trying to run a sql script that was written by a third party to set up the tables and stored procedures of a 6.5 database. The script crashes
about half way through. The error looks like this...

The vendor said that I needed to change the client configuration to tcp/ip sockets instead of the named pipes that I was using. When I change
this setting I can no longer connect to the server through enterprise manager. The error message is...

I am not sure if the db-library is not set up correctly for the server or why it will not connect through the tcp/ip socket. I ran the ping command
from the command line and it returns a valid connection to the server. This doesn't make a lot of sense to me.

Does anyone have any experience with this problem or suggestions of something to try to fix this problem. Any ideas would be great. Thanks.

View 2 Replies View Related

Named Pipes Vs Tcp/ip

Oct 20, 1999

Sorry I forgot to attach the error messages.

This is the message when the script crashes...

DB-Library: Unexpected EOF from SQL Server. Connection broken.
Net-Library error 109: ConnectionCheckForData (PeekNamedPipe()).

DB-Library Process Dead - Connection Broken



This is the message when I try to connect to the server using tcp/ip sockets...

A connection could not be established to SERVERNAME - [DB-Library]Unable to connect SQL Server is unavailable or does not
exist. General network error.

View 1 Replies View Related

Named Pipes?

Aug 9, 2003

Hi Everybody,

Can anyone tell me what are named pipes? and what is a TCP/IP net library? Do these libraries work together or only one can be used at a time?

Thanx in advance.

Regards,

Samir.

View 1 Replies View Related

Named Instance

Sep 4, 2003

I am running SQL2k SP3 with a default instance. I have recently added a second, named instance. When I try to connect to the named instance through Enterprise manager, it often times out. I never get a timeout accessing the default instance. Is there something I missed when setting up the second instance that is causing this access delay?

Thanks,
Ken Nicholson

View 1 Replies View Related

Named Pipes Vs. TCP/IP

Feb 11, 2004

Can anyone please tell me why i have one SQL 2000 server that is only accepting Named Pipes connections from clients? In the Server Network Configuration Utility both protocols are enabled, although Named Pipes in first and i cannot change the ordering.

TIA:confused:

View 6 Replies View Related

Named Sets???

Dec 18, 2004

Hi,

Can someone briefly explain which this feature of Analysis services is and how canit be of use.

Also, besides BOL, I'll appreciate if someone can recommend a good reading on this.

Thanks.

View 2 Replies View Related

SQL Using Named Pipes Not TCP/IP

Apr 20, 2005

All - I have 2 SQL2K clusters, both have local applications and replication. The first cluster uses TCP/IP for all its connections when looking at SQL Management processes. The second is still using named pipes for all connections. How do I set or configure the second cluster to use TCP/IP on local connections and replication? Thanks.

View 4 Replies View Related

Named Instances

Feb 14, 2002

Is it possible to rename a named instance?

We have a vendor product that can only handle up to 18 characters for the server name and the instance name (we did not know this when we created the named instance) and of course our combined name is 19 characters, so it trys to pass the 18 charater name and then fails with the following error:

[LOG] Unable to read local eventlog (reason: The data area passed to a system call is too small)

So, can i rename the instance, or do i have to create a new named instance?

Thanks

View 2 Replies View Related

Named Pipes Vs. TCP/IP

May 23, 2001

In your experience and all things being equal, is connectivity using named pipes faster, slower, or the same as tcp/ip?

View 1 Replies View Related

Named Pipes Vs TCP

Dec 13, 2004

How would one go about adjusting the default connection to the SQL Server on the client side, using either Named Pipes or TCP as the default?

View 4 Replies View Related

Named Instance

Mar 18, 2008

What is the easiest way to check wether a server has named instance or not? Is there a command from DOS?


------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related

There Is Already An Object Named

Aug 22, 2007

Don't believe you can not do this


if @v = 1

begin

select * into #temp1 from dbo.Table11 where col1 = @v

end

else

if @v = 2 begin

begin

select * into #temp1 from dbo.Table11 where col1 = @v

end


Error : There is already an object named '#temp1' in the database.

Compiler should know that at any time only on condition is going to run.

View 3 Replies View Related

Need Help To Create This Named Set

Feb 28, 2007

I have a "Date" dimension with several attributes in it such as "Accound Period", "Calendar Year", "Completed Year"etc. I want to create a "named set" for some dataset to filter out the "Calendar Year" which has the value "Less than or equal to" the value of the attribute "Completed Year".

The structure for the "Date" dimension is as follows:

Calendar Year (attribute)

Members Calendar Year------member Properties



Completed Year (attribure)

Members Completed Year------member Properties

It has only a single value for the "Completed Year" and it was calculated in the data source view using "getdate() -2" ie. 2005 for the entire attribute. How to create a named set to filter out the Calendar Year with this? Thanks.





View 5 Replies View Related

Named Pipes Error

Apr 30, 2008

I'm working on an insert into a database and I barrowed this code stright from Microsoft, changing the info I need. I keep getting an error "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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" This is the same connection string that I use for querying the database without a problem. Anyone have any ideas?
Jeff
SqlConnection con = new SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=software.mdf;Integrated Security=True;User Instance=True;");SqlCommand cmd = new SqlCommand("INSERT INTO download_table (username, computer_name, computer type, install type, date, email, agree, title) VALUES (@username, @computer_name, @comptype, @installtype, @date, @email, @agree, @title)", con);cmd.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 50));cmd.Parameters["@username"].Value = username;cmd.Parameters.Add(new SqlParameter("@computer_name", SqlDbType.VarChar, 50));cmd.Parameters["@computer_name"].Value = computer_name;cmd.Parameters.Add(new SqlParameter("@comptype", SqlDbType.VarChar, 50));cmd.Parameters["@comptype"].Value = comptype;cmd.Parameters.Add(new SqlParameter("@installtype", SqlDbType.VarChar, 50));cmd.Parameters["@installtype"].Value = installtype;cmd.Parameters.Add(new SqlParameter("@date", SqlDbType.DateTime, 50));cmd.Parameters["@date"].Value = date;cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 50));cmd.Parameters["@email"].Value = email;cmd.Parameters.Add(new SqlParameter("@agree", SqlDbType.VarChar, 50));cmd.Parameters["@agree"].Value = agree;cmd.Parameters.Add(new SqlParameter("@title", SqlDbType.VarChar, 50));cmd.Parameters["@title"].Value = title;cmd.Connection.Open(); <--- This is where I get the above errortry{cmd.ExecuteNonQuery();}catch (SqlException ex){}cmd.Connection.Close();

View 7 Replies View Related

Named Pipes...ASAP Please

May 21, 2001

Hi,

How do I find out on which path is the SQl server lisytening to on named pipes when I look at the registry?

Thanks,
Ganesh

View 1 Replies View Related

Named Pipes Error...

Jun 28, 2001

Can anybody help?
I have recently got any error -
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection Broken
/

Does anybody know how to resolve or point me in the right direction.....?

Thanks

View 4 Replies View Related

Database Named SUSPECT????

Jan 25, 2001

Dear friends,

I had a huge problem with my SQL database and I had it restored by using my back up tapes from the days before. I tried to back it up although it allowed me to restore it...it now says that the database is "suspect"= not found,,lost?... How can I get rid of this suspect & open my database,,, Does this mean that my database cannot be restored from a prior day (using my backup tapes from past days through the server?)

Can someone please help me concerning "suspect database names in SQL Serbver 7.0?

Any ideas ...please help me ...more detail?

Thanks in advance,
Brad Isaacs
Junior VB Developer / SQL Server 7.0 databases

View 4 Replies View Related

A Table *named* &#34;dbo.tSales&#34;

Mar 23, 2000

Here's a good one:

I have a table *named* "dbo.tSales", in addition to being owned by dbo. The fully qualified name would look like "databasename.dbo.dbo.tSales".

In other words, the name starts with "dbo." and shows up in the output when you do:

select name from sysobjects where name like '%Sales' and type = 'u'
name
------------------------------
dbo.tSales
tSales


"tSales" is a real table, also owned by dbo. I don't know how the bogus table got there. The only reason that I've noticed this is because Backup Exec tries to backup this table by explicitly naming it in a DUMP TABLE statement and the statement fails on syntax because the fully qualified name has too many periods in it.

I can't select from dbo.tSales, rename it or drop it using TSQL or SEM. All methods fail because they are expecting that the tablename cannot contain any periods, since the period is a delimiter.

My question: How can I get rid of this table?


-darin!

View 2 Replies View Related

Using Named Calculated Columns

Dec 13, 2002

I'm trying to create calculated columns in a SELECT query (inside a sp) that are based on other calculated columns in the same SELECT list. But since I'm not allowed to refer to each calculated column by name, I'm forced to repeat long calculations every time I build one calculation based on another.

I'd like to do something on the order of:
SELECT Price*Quantity AS SalePrice, SalePrice*1.08 AS SalesPriceWithTax
FROM Orders

Access used to let me do that, but not SQL. Any suggestions? Can I use variables declared and assigned before I start my SELECT, and then put those variables in my SELECT list? Anyone have a useful shortcut?

View 6 Replies View Related

SQL Server Named Instances

Apr 11, 2006

Informatica jobs were erroring out with a sql server driver error
while connecting to a named instance in SQL Server.
The issue was because in sql server named instance, the port was allocated dynamically and Informatica 7.1.4 (provides 4.21 version of Datadirect odbc drivers for SQL server) which dont have support for named instances with dynamic port allocation. This feature is only present from version 5 of the Datadirect odbc drivers for SQL server
To fix this issue, I need to allocate the port for the named instance instead of getting it dynamically.
My questions
1) Whats the impact of doing a port allocation for the named instance on the existing environment.
2) Is there any issues which we can foresee in the applications
3) Things which i need to be aware of
Thanks in advance
sreenath

View 3 Replies View Related

Connecting To Named Instance

Jan 3, 2004

Hi,

How can i connect to Named Instance of MSDE on remote server from Enterprice Manager in my local computer ?

Instance name is Helm

I can connect inside server like

osql -S -E .Helm

But how i access from remote machine using Enterprice Manager ?

Regards,

Yujin Boby

View 1 Replies View Related

Named Pipes Under Alias

Feb 17, 2004

I have a SQLSERVER INSTANCE A, to which I want to connect from a Client as TEST, using the SQL Server alias functionality under the client network utility program. The alias is working fine with the TCPIP connection but it fails when I try to connect to the SQL Server using the named pipes. In my case I'm trying to connect to a named instance of SQL Server 2000 using the following pipe: \servernamepipemssql$instancenamesqlquery

Any help?

View 2 Replies View Related

License And Named Instance?

May 26, 2004

Hi, all.
To use Named instance on the same machine, Do I need to check any license issue?
My Company has one server license and wants to use Named instance.
thank you..

View 2 Replies View Related

SQL Server Port.... (Named

Jul 12, 2006

I have access to the default instance of a SQL Server ..... I have access to the QA only .. by reading the registry through the QA I get to knw that there are additional 4 more instances on the Server ..... is is possible to get the port on which they are listening using the QA ....

I knw that we can go to the servers network utility & see it there in the TCP /IP ... but i dont have access to that , neither do i have access to the error logs that tell me , on which port is SQL Server listening to

Can someone help

View 1 Replies View Related

Named Pipes Question

Aug 14, 2007

i have to admit I am not that familiar with named pipes, but i do have an issue with them that I would like to understand a bit better.

I installed the SQL 2005 express version on a machine and using a express manager i tried connecting to the instance i just installed. However i was getting an error message referencing the NAMED PIPES. after hours of trying to figure out what the problem was i found that using the SQL Server COnfiguration Manager, under the SQL Server 2005 Network Configuration there is a node called Protocols for SQLEXPRESS. there are four protocols, one of which is the NAMED PIPES. Under the properties for this protocol there is the PIPE NAME which is what i had to change to "\.pipesqlquery". the default was longer and if i remember it correctly it was something like "\.pipeMSSQLSERVEREXPRESSsqlquery".

Once i made that change i was able to connect. Can somebody please explain to me what I have done (i can't find the article that lead me to this) and if there is any way to make this change in some sort of a script or something.
reason being is that I have an VB app that deploys the SQL 2005 express version as well and i would like to eliminate this step from the user to configure before using the app.

many thanx

View 4 Replies View Related

IP Address And Named Instances

Dec 4, 2007

Hi everyone,

Is there a way to route a specific IP address to a named instance on a SQL 2k system???

thanks
tony

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved