SQL Server Full Text Search
How do I setup SQL full search in SQL Server Express 2005 and 2008? Are there any helpful queries that a dba should know? ...
sql
Is it possible to execute SQL commands and send MSMQ messages in a TransactionScope?
I'm investigating using MSMQ for my team's new project but I need to know if I can send MSMQ messages and execute SQL commands within a System.Transactions.TransactionScope and have them commit or rollback together. I can't find a reliable source online that says "yes" with code examples. I need to send some messages to a single queue ...
How to remove a field from SQLServer2005 table
I tried this: ALTER TABLE My.Table DROP MyField and got this error: -MyField is not a constraint. -Could not drop constraint. See previous errors. There is just one row of data in the table and the field was just added. EDIT: Just to follow up, the sql was missing COLUMN indeed. Now I get even more seriously looking errors though:...
How can I drop a table if it exists in SQL Server 2000?
I have a DTS package that drops a table then creates it and populates it but sometimes something happens and the package fails after the drop table. If it's rerun it fails cuz the table hasn't been created yet. Is there something like "if exists" for SQLServer 2000 like in MySQL? thanks. Edit Thanks everyone! I went with TrickyNix...
how to pass variables like arrays / datatable to SQL server ?
Sometimes you need to upgrade the database with many rows that you have in a datatable or you have an array full of data, instead of putting all this data together in a string and then splitting in SQL SERVER, or instead of iterating the datatable in the code row by row and updating database, is there any other way? Is there other type o...
Howto? Parameters and LIKE statement SQL
I am writing a searching function, and have thought up of this query using parameters to prevent, or at least limit, SQL injection attacks. However, when I run it through my program it does not return anything: SELECT * FROM compliance_corner WHERE (body LIKE '%@query%') OR (title LIKE '%@query%') Can parameters be used like this? or ...
Select one column DISTINCT SQL
Added: Working with SQL Server 2000 and 2005, so has to work on both. Also, value_rk is not a number/integer (Error: Operand data type uniqueidentifier is invalid for min operator) Is there a way to do a single column "DISTINCT" match when I don't care about the other columns returned? Example: **Table** Value A, Value L, Value P Valu...
MS SQL 2005 Table to XML
I have a simple table in SQL Server 2005, I wish to convert this to XML (using the "FOR XML" clause). I'm having trouble getting my XML to look like the required output. I've tried looking through various tutorials on the web, but I am struggling. Can someone help? The table I have looks like this TYPE,GROUP,VALUE Books,Hardback,56 Bo...
Stored procedures or inline queries?
First of all there is partial question regarding this, but it is not exactly what I'm asking, so, bear with me and go for it. My question is, after looking at what SubSonic does and the excellent videos from Rob Connery I need to ask: Shall we use a tool like this and do Inline queries or shall we do the queries using a call to the stor...
In django, how do I sort a model on a field and then get the last item?
Specifically, I have a model that has a field like this pub_date = models.DateField("date published") I want to be able to easily grab the object with the most recent pub_date. What is the easiest/best way to do this? Would something like the following do what I want? Edition.objects.order_by('pub_date')[:-1] ...
When is SqlConnection.RetrieveStatistics() useful?
What are the problems that calling this method can help with? Do you ever use it in debugging you data access? ...
ASP.NET Formview Item Inserting Event
Hi All, I'm using the FormView control in ASP.NET for a simple form to insert into a MS SQL DB. I have an event for onItemInserting to set some values behind (such as time stamp, etc) and was curious how to check some user entered values in the onItemInserting event and cancel the item from being inserted. The reason I want to do it in ...
No indexes on small tables?
"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil." (Donald Knuth). My SQL tables are unlikely to contain more than a few thousand rows each (and those are the big ones!). SQL Server Database Engine Tuning Advisor dismisses the amount of data as irrelevant. So I shouldn'...
Dynamic patching of databases
Please forgive my long question. I have an idea for a design that I could use some comments on. Is it a good idea to do this? And what are the pit falls I should be aware of? Are there other similar implementations that are better? My situation is as follows: I am working on a rewrite of a windows forms application that connects to a SQ...
Full outer join in django
How can I create a query for a full outer join across a M2M relationchip using the django QuerySet API? It that is not supported, some hint about creating my own manager to do this would be welcome. Edited to add: @S.Lott: Thanks for the enlightenment. The need for the OUTER JOIN comes from the application. It has to generate a report...
Looking a generic Python script to add a field and populate the field with conditions
I am looking for a script to allow users to add a text field to a .dbf table(e.g. landuse categories) and allow them to input/update the rows basing on what values in the GRIDCODE (numeric categories) field they think should be assigned into text categories.i.e. if GRIDCODE value is 4, the corresponding field value of landuse/landclass ...
Help With Basic SQL Query
I have a table with columns ID, DateStamp and the ID need not be unique. How do I write a query that will give me a list of IDs with the minimum DateStamp and the maximum DateStamp? So, for example, for a given ID, the output may look like: 938423, 1/1/2000, 12/13/2003 [I am running SQL Server 2000.] ...
SQL Searching by MAX()
select max(DELIVERY_TIMESTAMP) from DOCUMENTS; will return the time that the latest document was delivered. How do I return the other columns for the latest document? For example I want DOC_NAME for the document that was most recently delivered? I'm not sure how to form the WHERE clause. ...
Can I use recursion in a Sql Server 2005 View?
I tried to use OPTION (MAXRECURSION 0) in a view to generate a list of dates. This seems to be unsupported. Is there a workaround for this issue? EDIT to Explain what I actually want to do: I have 2 tables. table1: int weekday, bool available table2: datetime date, bool available I want the result: view1: date (here all days in this...
Help with sql join
I have two tables: Table 1: ID, PersonCode, Name, Table 2: ID, Table1ID, Location, ServiceDate I've got a query joining table 1 to table 2 on table1.ID = table2.Table1ID where PersonCode = 'XYZ' What I want to do is return Table1.PersonCode,Table1.Name, Table2.Location, Table2.ServiceDate, I don't want all rows, In table 2 I'm only ...