Lets say I have the query: SELECT Foo FROM Bar Which returns Foo A B C What I really what is: Foo A,B,C So all of the values from all of the rows has been collapsed into a single row (the commas are optional). Is there a way to use a select statement to do this because I do not want to use cursors? ...
I'm trying to write a stored procedure that will return two calculated values for each record according to the rules below, but I haven't figured out how to structure the SQL to make it happen. I'm using SQL Server 2008. First, the relevant tables, and the fields that matter to the problem. ProductionRuns RunID (key, and RunID is giv...
Basically I want to use PRINT statement inside a user defined function to aide my debugging. However I'm getting the following; Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function. Can this not be done? Anyway to aide my user defined function debugging? Cheers ...
I have a Products table in a SQL Server database and I am having to troubleshoot a legacy stored procedure that uses Full-Text indexing. For our purposes here, lets suppose that the Products table has two fields ID, Keywords. And the Keywords field is populated with the following: ROLAND SA-300 This Roland SA-300 is in MINT conditi...
OK, I know it can be done, I do it quite often, but why so difficult to do a loop in T-SQL? I can think of a ton of reasons I'd want to parse thru a query result set and do something that simply can't be done without a loop, yet the code to setup and execute my loop is > 20 lines. I'm sure others have a similar opinions so why are we st...
I have the following code: -- start of code set noexec off declare @requiredVersion int declare @currentVersion int set @requiredVersion = 5 set @currentVersion = 4 if (@currentVersion < @requiredVersion) begin print 'Please update your DB to version 5 before running this script.' set noexec on end go -- print 'Dummy' insert...
Hello All, I recently built a query in SQL that I can use to look at our payment log and find out an average number of payments made per hour over a period of time. I'm sure there are third party reporting applications that are much more suited for doing the type of calculation I'm trying to do, but just for fun, I'm kind of curious t...
We have a large database with enquiries, each enquirys is referenced using a Guid. The Guid isn't very customer friendly so we want to the additional 5 digit "human id" (ok as we'll very likely won't have more than 99999 enquirys active at any time, and it's ok if a humanuid reference multiple enquirys as they aren't used for anything im...
I need to add a row dynamically in SQL after the Marketer number changes with the header "Marketer Total" that should add only the "Total" column. For example, after the last row of Marketer 22 row, there should be "Marketer Total" and then under the Total column should be 1804. The same should occur after the last row of Marketer 500....
I am trying to grow a database using the following the code below. However I get the following error. How do I check for size (3600MB) and grow it if necessary? USING MyDatabase ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase_data, SIZE = 3600MB) Error: MODIFY FILE failed. Specified size is less than or equal to current si...
We have an Oracle application that uses a standard pattern to populate surrogate keys. We have a series of extrinsic rows (that have specific values for the surrogate keys) and other rows that have intrinsic values. We use the following Oracle trigger snippet to determine what to do with the Surrogate key on insert: 'IF :NEW.SurrogateK...
How can I change the class name of stored procedure result generated by LINQ to SQL designer (besides messing with designer.cs)? Also, how can you perform a linq query on the result set of the stored procedure? ...
I'm doing a large import from a comma delimited file and want to lookup an employer id during the sproc that is executed at the end of the import process. The issue I'm having is that my LIKE doesn't seem to work ... so I wanted to see if the syntax is correct. Note - This will update all the records = the employer name but anything LI...
I'm trying to retrieve all rows inserted during a specific month. SELECT dbo.Post.UserId, dbo.Post.Tags, dbo.Post.CommentCount, dbo.Post.Status, dbo.Post.PostDate, dbo.Post.[Content], dbo.Post.Title, dbo.Post.PostId, dbo.[User].DisplayName FROM dbo.Post INNER JOIN dbo.[User] ON db...
Hi, Please can someone show me how I would return the column names of a table using SQL server 2008? i.e. a table contains the columns id, name, address, country and I want to return these as data? Thank you ...
Hi there, I've got a very strange sql-related problem. I'm accessing a MSSQL Server 2005 with PHP (odbc), when I profile the sql statement the following is executed: declare @p1 int set @p1=180150003 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=-1 exec sp_cursoropen @p1 output,N'SELECT fieldA, fieldB,...
I have this query which is pretty long, but adding a where clause to it, or joining on a string makes it take an extra 2 seconds to run. I can't figure out why. Here's the query in full: ALTER PROCEDURE [dbo].[RespondersByPracticeID] @practiceID int = null, @activeOnly bit = 1 AS BEGIN SET NOCOUNT ON; select isnul...
What is the best practical way of learning index tuning while writing tsql queries? I have VS2008 SQL Express. Could someone please provide me examples, etc? I have already found online articles and they are great in theory, but I still fail to see index tuning in real life action. Are there small easy to create examples out there? ...
Hi, I have a union all query in a stored procedure. WHat I would like to do is Sum a column and return that query to the client How would I do this? Malcolm ...
I want to do a case on the result of 2 columns. How do I do this? e.g.: SELECT CASE amount=100 AND DATE IS NOT NULL WHEN 0 THEN 'Something' ELSE '' Something like that? ...