I need to index a varchar field on my table in MS SQL Server 2005, but it's not clear to me how to do so. If I try to add a non-clustered index on the field, it says "Column 'xxxx' in table 'mytable' is of a type that is invalid for use as a key column in an index" My table has an auto-increment int ID that is set as the primary key on...
I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. Th...
I have db table with parent child relationship as: NodeId NodeName ParentId ------------------------------ 1 Node1 0 2 Node2 0 3 Node3 1 4 Node4 1 5 Node5 3 6 Node6 5 7 Node7 2 Here parentId = 0 means that it is a root level node. N...
I need to develop an application which stores data in a SQL Server 2005 database (the app itself will be either a WCF Service or an Asp.Net Web Service). Now, this data is supremely confidential, and I need to have it stored in an encrypted form in the database. So, I am wondering what the best practices are around this. I know that th...
I need to make DISTRIBUTED TRANSACTION between two MS SQL Server 2005, it drops an error that NO ACTIVE TRANSACTION IS PRESENT or smth like this. DTCTESTER programs drops an error that transaction could not be done. btw 135 port is open on the firewall. Maybe anyone knows what a problem, or have a suggestion? ...
I need to run a RLIKE query on a database mmoved from MySQL to SQL Server 2005 however having problems replicating the "RLIKE" functionality that MySQL provides. Does anyone have any ideas? Cheers, Chris ...
I have backed up an encrypted DB (symmetric key/certificate) and restored it on a different server. Unfortuantely we're having problems with the decryption... hoping someone can help. In the restored db, I can see the Symmetric Key and the Certificate in SSMS, but when I try to Open the key using the cert ( open symmetric key Key...
There is a store procedure that uses FREETEXTTABLE twice on two tables and then merges the results and returns the top 50. The problem is if I do a search on "Women of Brewster", the results returns "Confession of an ex doofus motha" with a rank of 143 from table A and second "Women of Brewster Place" with a rank of 102 from table B. ...
Hi, i know this should be db 101, but its just not as clear as it can be for me. I am using SQL2005 express and i want to copy data from databaseA to databaseB. DatabaseB already contains existing data - it may even contain data with pk's that have changed or dont exist - for example: DataBase A pk1 = peaches pk2 = apples DataBase B ...
I'm using Installshield 2008 express to create an installation package. My application relies on Sql Server 2005 express edition. I know I am able to check the "microsoft sql server 2005 express" prerequisite under the redistributables tab. However, this leaves the installation entirely in the hands of the end user, and if he/she chooses...
The reports I can churn out in SQL Server 2005 Reporting Services using the MS Business Intelligence Development Studio is rather limited for what I want to do. I have had a look at a similiar stackoverflow question and have had a browse through the MSDN website. I have managed to add some additional functionality from examples I've foun...
I want to check in Transact SQL if a specific column in a table has statistics and if so to get them all. ...
Hi Guys We have an Access DB which has a set of local tables and input forms etc. in which a user maintains their data. We also have a SQL DB with the same tables which is used to displays the data in a web search form. What is the best way to allow the user to udate his changes to the SQL db while keeping the working copy local so he...
(sql server 2005) A person uses their cell phone multiple times per day, and the length of their calls vary. I am tracking the length of the calls in a table: Table: Calls [callID, memberID, startTime, duration] I need to a query to return the average call length for users PER DAY. Per day means, if a user used the phone 3 times, fi...
Let's say I have a table that has a column of XML type data. Within SQL, I can execute the following statement: select top 10 *, Content.value('(/root/item/value)[1]', 'float') as Value from xmltabletest where Content.value('(/root/item/MessageType)[1]', 'int') = 1 The result set contains only the records matching ...
My PHP/MS Sql Server 2005/win 2003 Application occasionally becomes very unresponsive, the memory/cpu usage does not spike. If i try to open any new connection from sql management studio, then the it just hangs at the open connection dialog box. how to deterime the total number of active connections ms sql server 2005 ...
I plan to use MS SQL Server 2005 application roles in my application. I will start the role by executing the *sp_setapprole* and finish by executing the *sp_unsetapprole* SPs. The application is implemented in ASP.NET. I've read that connection pooling doesn't work with application pooling and there is no way to react on connection disc...
I need to migrate Access databases to SQL Server 2005. Since this needs to be done from within a setup so that a customers' installation is transparently migrated to SQL Server 2005, I wonder if it is possible to automate the SSMA toolkit from Microsoft. ...
I have looked at some resources already and just want to clarify and get an opinion. First of all to totally avoid any problems we could just not bother using identity columns as primary keys instead have them generated ourselves and just have those values replicated both ways presuming they are always unique at any time of creation. F...
I've recently taken over development on a SQL Server 2000 database that needs some help. We're planning on upgrading it to SQL Server 2005 soon. This database has no audit fields on the tables (CreatedBy, CreatedDate, etc.), no foreign keys, and terrible overall design. There are half a dozen programs that directly access the database...