How to do SELECT TOP @Param in a Stored Procedure?
I'm trying to do the following in a proc, but getting an incorrect syntax error: SELECT TOP @NumberOfResultsToReturn * What am I doing wrong here? Thanks. ...
sql
Performance Tuning PostgreSQL
Keep in mind that I am a rookie in the world of sql/databases. I am inserting/updating thousands of objects every second. Those objects are actively being queried for at multiple second intervals. What are some basic things I should do to performance tune my (postgres) database? ...
Compare 2 values of different types inside of subquery
I am using a MS SQL db and I have 3 tables: 'base_info', 'messages', 'config' bases: ID Name NameNum ==================================== 1 Home 101 2 Castle 102 3 Car 103 messages: ID Signal RecBy HQ ============================ 111 120 Home 1 111 110 Castle 1 111 125 Car...
SQL CLR SqlBulkCopy from DataTable
We have an in memory DataTable in a CLR procedure. After significant processing the DataTable has a lot of data that we need to load into a table in the database. Unfortunately, since we are using a context connection SqlBulkCopy will not work (throws error: The requested operation is not available on the context connection outline of c...
Why is LINQ to SQL inserting duplicates into my table?
I have a web service that retrieves reviews from my database and if not enough reviews are returned, it goes to a secondary source and gets them there and caches them in my database. I have recently noticed that it has been creating duplicates in my database despite me having primary keys to prevent it. I was thinking that maybe when I m...
Update Value in Table from another table.
I realized that i was using a varchar attribute as a index/key in a query, and that is killing my query performance. I am trying to look in my precienct table and get the integer ID, and then update my record in the household table with the new int FK, placed in a new column. this is the sql i have written thus far. but i am getting a ...
A newbie question about database transactions
I know that database transactions are used to ensure all the statements in the transaction will be executed or none of them will. But what about locking and thread safety, for example if I have an sproc which affects multiple tables and I use a transaction in that sproc, that sproc is called from different clients at the same time, will...
LIMIT the return of non-unique values.
I have two tables. Posts and Replies. Think of posts as a blog entry while replies are the comments. I want to display X number of posts and then the latest three comments for each of the posts. My replies has a foreign key "post_id" which matches the "id" of every post. I am trying to create a main page that has something along the...
create xml file from sql script
how to Generate xml file in sql server 2005 using tsql ...
SQL Query to check if 40 columns in table is null .
How do I select few columns in a table that only contain NULL values for all the rows? Suppose if Table has 100 columns, among this 100 columns 60 columns has null values. How can I write where condition to check if 60 columns are null. ...
Query for SQL Date in C#
Let's say I have a table with a Date column. In my C# application, I want to be able to query for rows using the LIKE operator on the DateTime column. My problem is, how do I handle the regional settings? For example, if the current regional settings are set to de-DE, the date format of windows is dd.mm.yyyy. Now the user might enter so...
SQL to search many to many relationship with acts_as_tsearch
I'm using the acts_as_tsearch plugin for rails to do full text searching with Postgresql. They give an example of multi table search here: http://code.google.com/p/acts-as-tsearch/wiki/ActsAsTsearchMethod But that only does a one-to-many relationship. I'm trying to get it to also search a many-to-many relationship (has_and_belongs_to_...
Problems with Turkish SQL Collation (classic "I" problem)
Hi, I'm having problems with our MSSQL database set to any of the Turkish Collations. Becuase of the "Turkish I" problem, none of our queries containing an 'i' in them are working correctly. For example, if we have a table called "Unit" with a column "UnitID" defined in that case, the query "select unitid from unit" no longer works beca...
Reporting Hierarchy in Mutltiple Tables
I am looking at quite a monstrosity of an application that uses several tables to represent a reporting hierarchy, but eacvh of these tables is identical. The table at the bottom of the hierarchy has the most records, each of which has a ParentID to the rollup table above it, eventually all adding up to only one total in the top rollup ...
LinQ to SQL and CLR User Defined Types
Hi there, I have created a User Defined Type in .Net 3.5 as per my blog entry at : http://jwsadlerdesign.blogspot.com/2009/04/this-is-how-you-register.html This works fine when using SQL with technologies like nHibernate. However, when I try to map my LinQ to SQL class to use this UDT (with attribute defintions not XML), and I setu...
Finding free slots in a booking system
In my question about searching for date ranges I tried simplifying the problem and inadvertently posed a different and simpler problem. Rather than complicate that question with an edit I am going to ask the problem I actually intended. I have two tables Property and Booking. Bookings have a foreign key to Properties and also start an...
Problem with sql query. Should i be using union?
Have a problem that seems easy on paper but i'm having a big problem figuring out how best to write a single query. I have a table CREATE TABLE `profile_values` ( `fid` int(10) unsigned NOT NULL default '0', `uid` int(10) unsigned NOT NULL default '0', `value` text, PRIMARY KEY (`uid`,`fid`), KEY `fid` (`fid`)...
Standard Function for Escaping Strings in SQL
I'm writing a program in .NET2.0 and I need to escape the inputs before using them. Unfortunately the standard parameter method system does not fully work in the system I'm using. Using the ODBCCommand class I cannot place a ? parameter in the select part of the statement (which is required for the little bit of trickiness I'm doing) w...
LINQ To SQL Paging
I've been using .Skip() and .Take() extension methods with LINQ To SQL for a while now with no problems, but in all the situations I've used them it has always been for a single table - such as: database.Users.Select(c => c).Skip(10).Take(10); My problem is that I am now projecting a set of results from multiple tables and I want to p...
Problem converting very small doubles to SQL floats
I'm trying to store a C# double in MS SQL 2005 as a float. However, there seem to be a range of small numbers which are valid doubles but which aren't valid floats. For example, when I try to store the value 1e-320 I get the error "the supplied value is not a valid instance of type float". This is consistent with the documentation for S...