'_WA_' Named Indexes
Sep 27, 1999I 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, 1999Now 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 RelatedRemoval Of Selected Indexes / Script Index Create For List Of Indexes
Jul 1, 2014I'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?
A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.
Sep 18, 2007So 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?
SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table
Jun 25, 2015I 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] ....
Indexes Vs Clustered Indexes
Sep 17, 2006What is the difference please?
View 1 Replies View RelatedSql Command Named : Contains
Mar 13, 2008I 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) )
Named Pipes Vs Tcp/ip
Oct 20, 1999I 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.
Named Pipes Vs Tcp/ip
Oct 20, 1999Sorry 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.
Named Pipes?
Aug 9, 2003Hi 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.
Named Instance
Sep 4, 2003I 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
Named Pipes Vs. TCP/IP
Feb 11, 2004Can 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:
Named Sets???
Dec 18, 2004Hi,
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.
SQL Using Named Pipes Not TCP/IP
Apr 20, 2005All - 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 RelatedNamed Instances
Feb 14, 2002Is 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
Named Pipes Vs. TCP/IP
May 23, 2001In your experience and all things being equal, is connectivity using named pipes faster, slower, or the same as tcp/ip?
View 1 Replies View RelatedNamed Pipes Vs TCP
Dec 13, 2004How 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 RelatedNamed Instance
Mar 18, 2008What 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
There Is Already An Object Named
Aug 22, 2007Don'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.
Need Help To Create This Named Set
Feb 28, 2007I 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.
Named Pipes Error
Apr 30, 2008I'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();
Named Pipes...ASAP Please
May 21, 2001Hi,
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
Named Pipes Error...
Jun 28, 2001Can 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
Database Named SUSPECT????
Jan 25, 2001Dear 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
A Table *named* "dbo.tSales"
Mar 23, 2000Here'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!
Using Named Calculated Columns
Dec 13, 2002I'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?
SQL Server Named Instances
Apr 11, 2006Informatica 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
Connecting To Named Instance
Jan 3, 2004Hi,
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
Named Pipes Under Alias
Feb 17, 2004I 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?
License And Named Instance?
May 26, 2004Hi, 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..
SQL Server Port.... (Named
Jul 12, 2006I 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
Named Pipes Question
Aug 14, 2007i 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
IP Address And Named Instances
Dec 4, 2007Hi everyone,
Is there a way to route a specific IP address to a named instance on a SQL 2k system???
thanks
tony