I suppose this is more an RFC than a true question, but I've come to realize "bit" data types do not optimize a query when they are used in a WHERE clause. Since they cannot be indexed, it forces the analyzer to query an available index for rows based upon the non-bit constraints. The result is then scanned to match the values of the bit constraints of the query. So you're potentially doing a full table scan, or an index scan.
My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.
I've a 32 bits application that runs on 64 bits Windows Server+MS-SQL. I've created an ODBC DSN using c:WindowsSysWow64odbcad32.exe. Sp, the program recognize the ODBC but having problem connecting. The error look likes this: *** [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '<name>' in sys.server. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. ***
This is my first post in this forum, I have one application built in asp .net, through a ODBC connect to a external database (prinex). The application was running in a 32bits server, but the server had to stop and now there is running in a machine of 64 bits.
The driver that use in server of 32 bits only exists for this version, right now i have to use the same for the 64 bits machine, i can install and configure the DSN (using c:WindowsSysWow64odbcad32.exe).
But at the time of execute the aplicattion an error is displayed. ¿How can i solve this problem?, ¿There is a way to force the application to use 32 bits DSN? ¿There is an alternative solution for connect to the database? ¿Someone had the same problem?
This is the image of the error: http://img404.imageshack.us/img404/8168/errorwebbn1.png
If somebody can help me, i would be very grateful.
I have an vendor application need to use SQL Server 16 bits version for server and client. I installed the SQL Server 6.5 from my "Backoffice 20" CD ROM I was told that after my installation of SQL Server 6.5 on server, if there is a folder "BIN" and "BINN", that means that I have installed both the 16 and 32 bits version. Is this true? To install SQL Server Client, if I install from my server from the sql65i386, the 32 bits client will be installed to my client workstation. Which folder should I look for the 16 bits client installation?
How may extra bits does a NULL column contain.And how many extra bits does a varchar column contain.(I 've worked with Ingres and in that environment it both needed 2 extrabits)Bye,Arno de Jong, The Netherlands
I am setting up a new server with Windows Server 2003 x64 bits and SQL Server 2005 x64 bits. Under 32 bits of Windows we normally use /3GB and enable AWE under SQL Server (both SQL 2000 and SQL 2005) and allocate 6 GB of fixed memory to SQL Server.
Does any one know whether I should do the same thing under the x64 bits platform. I read some articles on the web that it is no longer required to use AWE under SQL 2005 x64 bits??
I developed an SSIS package on my computer (32bits). The package basically get data from excel file and save it to a sql database. I use an OLE DB connection to the excel file: Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;
other info: Run64bitRuntime : false
It works fine on my local computer. I use the DeploymentManifest to deploy the package on the server. (all validations are ok).
But when I run a job which run the package i get this: with job step : SQL Server integration Services Package:
Executed as user: LU1xp_cmdshell. ...on 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:29:04 AM Progress: 2008-03-06 10:30:20.22 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2008-03-06 10:30:20.26 Source: Data Flow Task Validating: 3% complete End Progress Progress: 2008-03-06 10:30:20.29 Source: Data Flow Task Validating: 7% complete End Progress Progress: 2008-03-06 10:30:20.32 Source: Data Flow Task Validating: 10% complete End Progress Progress: 2008-03-06 10:30:20.35 Source: Data Flow Task Validating: 14% complete End Progress Progress: 2008-03-06 10:30:20.35 Source: Data Flow Task Validating: 17% complete End Progress Progress: 2008-03-06 10:30:20.35 Source: Data Flow Task Validating: 21% complete End Progress Progress: 2008-03-06 10:30:20.35 Source: Data Flow Task Validating: 25% comple... The package execution fa... The step failed
With job step cmd system:
Executed as user: LU1xp_cmdshell. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:32:37 AM Could not load package "******" because of error 0xC0010014. Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors. Source: Started: 10:32:37 AM Finished: 10:34:08 AM Elapsed: 91.141 seconds. Process Exit Code 5. The step failed.
No more detail about the error.
There is something strange: When I execute the package via DTEXECUI on my local computer and i speciify the sql server where i deploied the package. It works. The same on the server doesn't work.
I'm trying to install a version of SQL server 2005 with reporting services on a vista 64 bits.
I've a licence for SQL server 2005 standard edition in French, my vista is in French, and I'm using visual studio standard edition in English to develop. I would like to develop reports using SQL server to include these reports in my applications developped with Visual Studio.
Wich version of SQL server should I install ? I've tryed to install SQL server express 2005 with advanced feature SP2 (english version) and then later the standard edition (French version) (each time uninstalling all SQL server components between tries), but I got problems with reporting services each time.
I got first a warming saying than reporting services is 32 bits and IIS is 64 bits, I so allowed IIS to run 32 bits ASP .Net, but after that, I now get a problem of .Net registration in IIS as warming message on the line concerning Reporting services during the install of SQL Server, and it doesn't even offer to install RS on the next window.
I reinstalled the DotnetFX64 to be sure, but still get the same message. If I look in the IIS the application pools are looking me to be normal, with the ASPNET 32 bit code allowed.
I have an Application developed on ASP, I got a few new servers 64bits, I installed microsoft WIN 2003 SR. When I get in into the web site I receive this errors:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/ConnectToDatabase.asp, line 17
I made some obvios checks, I checked that the ODBC really exists and we move the IIS 6.0 to run in 32bits mode as I found in some page.
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?
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?
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.
Does anyone have the idea why the Indexes with _WA.....(like _WA_Sys_au_fname_07020F21) gets created.I don't how this index got created. I did not create this Index. My Question is does the system creates these indexes or something else does this. Thanks Chak
Is there a way to tell how many indexes exist for an entire database, all I'm looking is for a count or generating a report list. any help would be appreciated, thank you
At present I have been assigned to create indexes to retrieve the information fast, from the table. The existing table doesn’t have primary key, foreign key and unique constraints but I found to many default indexes already created by the system. I would like to know how this happened? Please inform how to delete these default indexes. Further, inform me other possible ways for the faster retrieval in SQL sever 7.0, if there are any.
I would appreciate if you send me a step by step explanations for the above problems.
I have run into a snag on my development server. Queries that are selecting data based on indexed fields in a where clause are using the wrong indexes. They are arbitrarily using the clustered index which isn't in the select at all and causing big performance problems. I can run the same statements on my production server and it runs based on the proper indexes. I used query execution plans to determine that this was infact the case.
I run DBCC Checkdb everynight and it comes back with no errors. I also rebuild the indexes. We also don't receive any other errors inputting or updating data. This sounds like corruption to me but if it's something else I don't want to spend the night restoring from production if there is another reason.
Is there any way for me to find out when last indexes have been used so that the one I don't need can be dropped.And also the one's that are of no use at all. I need this as i am trying to dump all duplicated indexe . i know i can do this in ver 7
when executed sp_help tablename, I get lot of statistics and indexes like the following. Can anyone please tell me how it is generated automatically. as far i know statistics are generated only for primary keys. Can you please tell me what is clustered , hypothetical and the indexes starting with _WA supposed to be. Also there are lot of duplicate stats. Is it Ok to deletes those. _WA_Sys_is_platinum_0A9D95DB _WA_Sys_active_0A9D95DB nonclustered, statistics, auto create located on PRIMARY Active hind_c_33_15 nonclustered, statistics located on hind_c_37_1 clustered, hypothetical located
I have a question about speed and indexes. I have a static table (no updates except once a year). I want to be able to search data quickly on one column or many columns. I have created nonclustered indexes on each of the columns I search by. Is there anything else I can do to speed up my queries? Unfortunately all the searches involve using the like operator. I have even broken my table down into 2 smaller tables (Table A ~ 3 million rows, Table B 8 million rows).
I need to alphabetize PART of a union all query, and was told that this may be accomplished with an index. I tried joining two views, but it does not work! I have NO experience with indexes and need some help.
Here is my code:
Select id, country from countries where id = 6 union all Select id, country from countries where id <> 6
I want the country with id 6 (USA) to be the default on a drop down list, then all the other countries listed after it in alphabetical order. Can you do this with an index? If so, how do I proceed?
We recently updated some of the databases from MS Access 2000 to SQL Server 2000. In Access we had columns which were set to Indexed No Duplicates, these were not Primary key fields. Is this possible to set some indexes to prevent duplicates in SQL Server without creating performance issues?
