Hi there, I have a stored procedure that is throwing an 'Invalid column name' error for 'ContentMarginExVat'; SELECT CategoryTitle, ContentID, ContentTitle, ContentMarginExVat, ContentWeight FROM VWProductsCurrent WHERE ContentID = @ContentID I have checked both the VWProductsCurrents and the associated table that gets the data...
I have a table with a trigger (for update) to set the LastEditedTime field when a change if made - simple enough. I also have a trigger (after update) to set LastStatusChangedTime. The problem is as follows: Is there any performance issue if i use two triggers for update? or can i combine two triggers into one? I'm wondering how othe...
The practical implications of serving images from a database are easy to understand. On one hand it's consistent (since everything else is in the DB) and means that when you scale to multiple servers the files don't need replication. However it puts a huge burden on the database (time required to respond) and on the intermediat networ...
Not being a SQL expert, and also only being semi-competent in CTE, how can I code this statement use the resultset from the following subquery within the main query, as our SQL Server is 2000. declare @subcategoryConcatenate varchar(3999) set @subcategoryConcatenate = '' select @subcategoryConcatenate = @subcategoryConcatenate + pumpCa...
hi, I have a simple stored procedure like so: ALTER PROCEDURE [dbo].[spList_Report] @id INT AS SET NOCOUNT ON SELECT * FROM tblProducts as products WHERE product.intID = @id I have 2 user tables: MainUser and SubUser Both tables have a foreign key column productID which is related to the primary key int...
Hi, This query takes about a minute to give results: SELECT MAX(d.docket_id), MAX(cus.docket_id) FROM docket d, Cashup_Sessions cus Yet this one: SELECT MAX(d.docket_id) FROM docket d UNION MAX(cus.docket_id) FROM Cashup_Sessions cus gives its results instantly. I can't see what the first one is doing that would take so much longe...
Is there a way to prevent that a Cursor changes at runtime. If I have a cursor that iterates over all the users and meanwhile, in the processing of each user, I create some additional users, then the Cursor will also iterate over the newly created users... ...
I am trying to stop input of negative values to process in any of the ranges, rather if they are negative they should drop down to the end of the 1st case statement as 'Invalid'. This is not working as when I run a test against the input of (-1000) i get a row for <=50K. I am afraid my syntax is wrong, but not sure why. ALTER FUNCTION ...
Hey everyone, I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()). So far I've got the following script, which works fine provided that i've cleaned up all the nulls beforeha...
Hi, I have this query but for some reason the StartDate values are not getting filtered. What's wrong with it? Please let me know. SELECT * FROM TableA A WHERE NOT EXISTS( SELECT * FROM TableB B WHERE A.pId = B.pId and A.StartDate >= '20-JUN-10' ) ...
I have stored procedures that take a user's username (to log who makes changes to the database) in addition to other information (like name, ID, email, etc.). Within the stored procedure I look up the user's ID and store that in the table. The issue I am experiencing is that the Entity Table does not match the input of the stored proce...
does anyone know whats wrong with this nested select stament? It complains about missing )'s but i can't unerstand why it doesn't work (i've left off the other bits of the statment) Select (CASE WHEN REQUESTS.grade_id = 1 THEN (CASE WHEN ((date_completed-date_submitted)*24*60)<=30 THEN 'Yes' ELSE 'No' END) ELSE ...
I think I've got the format of Recursive CTEs down well enough to write one, but still find myself frustrated to no end that I cannot manually process one (pretend to be the SQL engine myself and reach the result set with pen and paper). I've found this, which is close to what I'm looking for, but not detailed enough. I have no problem t...
What would be the best way of doing this? select 'blah' as foo, CASE WHEN foo='blah' THEN 'fizz' ELSE 'buzz' END as bar As it is written right now I get an invalid column name 'foo' error. Is there anyway to do this where the select statement could be used as a view? ...
Hi Everyone, DB: SQL Server 2008 I have two servers A and B. I want to able to insert data from server A into server B using a particular user. I can't seem to find a syntax for doing that. Can anyone please help me out on this. Thanks ...
I need to count number of email addresses in a db that have 3 or less characters before the @ sign, for example [email protected]. The parsename function isn't present in Oracle and I'm not sure how to write a regexp for this. Any help would be greatly appreciated! ...
I'm storing some intervals in the SQL, example: id INT from DATE to DATE How can I check, using only one condition (if possible) if a NEW interval conflict with a existing one? Examples: |-----------| (from 1 to 5) |-----------| (from 2 to 6) |--| (from 3 to 4) |--| (from 7 to 8) Every interval (in th...
Hi I want to use ms sql 2005 to send email reminders to my customers. I want to send daily reminders that should run around midnight(when traffic would be low). So I think the best way to do this is through ms sql 2005 and I been looking into sql agent and database mail but I am unsure how to make a script that would dynamically get th...
How would I do a basic profit and loss statement in SQL? I have a sales table with a costs field, and summing that field should show the total profit Likewise I have a purchases table with a cost field, which should show the total money going out. To show profit, would I just SUM the total costs in sales, and subtract the total costs ...
Using Sql Server 2005. I have a long running update that may take about 60 seconds in our production environment. The update is not part of any explicit transactions nor has any sql hints. While the update is running, what's to be expected from other requests that occur on those rows that will be updated? There's about 6 million tota...