I'm dealing with an annoying database where one field contains what really should be stored two separate fields. So the column is stored something like "The first string~@~The second string", where "~@~" is the delimiter. (Again, I didn't design this, I'm just trying to fix it.) I want a query to move this into two columns, that would...
I have a table of suppliers, and tables Computers, Cameras, Displays all containing the SupplierID field. I am trying to do a T-SQL that will list all the suppliers, with a count of all rows. I can do them one at a time with: SELECT SupplierID, COUNT(dbo.Computers.ComputerID) as Computers FROM Supplier INNER JOIN Computers ON S...
Warning: This is a long question The database that I am pulling data from has the table structure like this Table: ClientSales ClientSalesId int identity (1, 1) (PK) ClientId int (FK) TermId int (FK) StudentType1Population int StudentType1Adjustment int StudentType1Sales int StudentType1SalesAdjustment int Student...
Why do I have to SET ARITHABORT ON when using xml in sql server 2005? I tried researching why I have to set this but couldn't find an answer that told me why. Only that it needs to be set. Here is the specific error message I get when I take out the SET ARITHABORT ON line: PARAMETER ERROR: INSERT LIST COULD NOT BE PARSED - INSERT fail...
I much prefer to code in t-sql using what is in effect an in-line join, rather than have a long list of the joins at the end of the stored procedure or view. For example, I code: SELECT PKey , Billable, (SELECT LastName FROM Contact.dbo.Contacts WHERE (Pkey = Contacts_PKey)), (SELECT Description FROM Common.dbo.LMa...
Hi, I am using this query: SELECT DISTINCT pat.PublicationID FROM dbo.PubAdvTransData AS pat INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID WHERE (pa.AdvertiserID = 31331) AND (pat.LastAdDate > 7 / 1 / 2009) ORDER BY pat.PublicationID And it returns 0 results. What I am trying to d...
I'm writing a search query in SQL Server 2005 for a discussion board. The proc takes several parameters, but most are "optional". There's one search field for Message Body, which I have a full text index on. Here's the problem.. If I pass in a value to search against with FreeText, the search works fine (thank you Microsoft). Howeve...
I am running into an error I am having trouble figuring out. I have 2 tables and I'm trying to copy data from one to the other (simplified view): MyTable ------- ID varchar(11) do not allow nulls Field01 numeric(6,0) allow nulls MyTable_Temp ------------ ID varchar(11) do not allow nulls Field01 numeric(6,0) allow nulls My query loo...
Is there a way to ensure, either through specifying order or by specifying dependencies, that one component will execute before another in WiX? Specifically, I'm trying to create a database, then, optionally, run a script on it. My Wix looks similar to this: <ComponentGroup Id="SQLServerComponentGroup"> <Component Id="SQLServerIs...
Thus far all I have done with SQL are simple select statements, but now I've also started learning about INNER JOINs, GROUP BY clauses and inner queries. I learned the concepts, but now I’d like to do some exercises using the Northwind database, so that such queries would become second nature. I tried to come up with good examples tha...
Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to n stored procedures. When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role. Here are the steps to apply permissions: select the object that you want to gra...
Hello, I would like to know how to explicitly set a timeout for the Ruby DBI ODBC driver, when connecting to SQL Server. I would like long running queries to simply timeout and cancel themselves, saving further server resources and Rails processes. This was happening while we were using the ADO based Ruby driver, but now that we've sw...
Consider the following two tables: User - UserID - UserName Group - GroupID - GroupName The obvious association is that Users will be in Groups. This by itself is a simple many-to-many join situation, so lets add a 3rd table: UserGroup - UserID - GroupID Under this textbook schema, I can easily include a specific User in a s...
I have an asp.net web application written in C# using a SQL Server 2008 database. The database contains events with information across a number of tables such as event, venue, city and bands playing. In the ideal world I'd like my users to enter a natural lanaguage type query in a single search box along the lines of "The Who in Brisban...
Salesforce’s secret sauce: It queries its databases with “The Multi-Tenant Optimizer" So exactly what could this practice be comprised of? ...
Hi folks, I've got a database in production for nearly 3 years, on Sql 2008 (was '05, before that). Has been fine, but it isn't very performant. So i'm tweaking the schema and queries to help speed some things up. Also, a score of main tables contain around 1-3 mill rows, per table (to give u a estimate on sizes). Here's a sample datab...
I have a SQL SELECT statement that will create its results in XML Format. I would like to INSERT into another table (Log via insert) every time a row is selected from the original SELECT Statement. Is there anyway to include an INSERT Statement inside of a SELECT Statement? SELECT cs_ads_StoreLocations.LocationGUID, *Bunch of S...
I am joining onto a freetexttable using a searchTerm parameter which is also used in a number of other freetexttables in the query. I would like to remove the city name from this parameter if it exists in the current row. Trying to use replace like this: freetexttable(Gigs, Name, REPLACE(@searchText, c.CityName, '')) gigkt ON g.GigID ...
I am planning to build a database for a school application. I have designed a database to hold time tables for all the classes. It should also relate to the teachers taking the subject for the class. Also should be able to handle a public holidays mentioned. Also the attendance of all the students and teachers. Please guide me with the...
Using SQL Server 2008. I created a new database, created a new user and mapped the user to the same login name. Gave the user all the roles available including db_owner. The user created a new table but when the user tried to select from the table, an error "The SELECT permission was denied on the object ...." showed up. Why doesn't th...