Why calling a user defined function needs the owner name when calling a stored procedure doesn't ?
Why calling a user defined function need the owner name when calling a stored procedure doesn't ? Please help! ...
tsql
How to: Using NHibernate ICriteria for grouping, fetching associations and using T-SQL functions
Hi! I want to create the following T-SQL statement: SELECT SUM (sa.Amount) as 'SumAmount', SUM(sa.Cost) as 'SumCost', gg.[Description] as 'Goodsgroup', Month(sa.[Date]) as 'Month' FROM SalesmanArticle sa INNER JOIN Article a ON a.ArticleId = sa.ArticleId INNER JOIN GoodsGroup gg ON gg.GoodsGroupId = a.GoodsGr...
Is it possible to switch to a database on a linked server using a 'USE' statement in SQL Server 2005?
I've tried the obvious: USE linkedServerName.databaseName Which gives me the error: `Could not locate entry in sysdatabases for database 'linkedServerName'. If something like this were possible, it'd save me a bunch of clicking around in management studio! ...
What is the best way to perform a manipulation with huge amounts of data in SQL Server ?
We need to perform the following operation in our database : There is a table A which has column B_ID that is a foreign key to the table B. There are many rows in the table A that have the same value of B_ID and we want to fix this by cloning the corresponding rows in B and redirecting the rows from A to them. All this is relatively si...
Why is my query so slow? (SQL Server 2008 full text search weirdness)
I have a table with a full-text indexed column MiddlePart. The table has around 600,000 rows. The following query is very fast (30 results, <1 second): select * from DomainName where contains (MiddlePart, '"antiques*"') OR freetext(MiddlePart, 'antiques') This query is also very fast (5 results, <1 second): select * from DomainNa...
difference between tsql, access sql and pl/sql
i am very confused please help to clarify these differences! are they all completely different languages? what is the overlap? ...
How to alter the boolean value in SQL Server select query?
Basically I want to alter the boolean value selecting from the table: e.g.: SELECT otherColumns, not ysnPending FROM table I need a column ysnPending = true if the value is false & false if the value is true. Is there any function available to alter the Boolean value or I should use IIf or CASE...? ...
mySQL Variables in SELECT
Is it possible to have a query such as the following: SELECT @threadIDvar = `threads`.`id` AS `threadID`, (SELECT `posts`.`timeDate` FROM `posts` WHERE `posts`.`threadID` = @threadIDvar) AS `postDate` FROM `threads` INNER JOIN `posts` ON `posts`.`threadID` = `threads`.`id` WHERE `threads`.`boardID` = 1 I have tried it but I get @threa...
What's wrong with my fulltext search query?
I'm have some trouble with the fulltext CONTAINS operator. Here's a quick script to show what I'm doing. Note that the WAITFOR line simply gives the fulltext index a moment to finish filling up. create table test1 ( id int constraint pk primary key, string nvarchar(100) not null ); insert into test1 values (1, 'dog') insert into test1 v...
check constraint custom message
Hi. Is there a way to perform data validation using CHECK constraints in t-sql and to show somehow the data which did not pass the check constraint? ...
T-SQL - Date rounding and normalization
Hi: I have a stored procedure that rounds a column with dates in (yyyy:mm:dd hh:mM:ss) to the nearest 10 minute handle (yyyy:mm:dd hh:mM) 20100303 09:46:3000 ------> 20100303 09:50 but i want to chage it to round it off to the nearest 15 minute handle: 20100303 09:46:3000 ------>20100303 09:45 here is my code : IF OBJECT_ID(N'[dbo...
Incorrect syntax near ')' calling storedproc with GETDATE
Maybe I am having a moment of 'afternoon', but can anyone explain why I get Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'. When running CREATE PROC DisplayDate ( @DateVar DATETIME ) AS BEGIN SELECT @DateVar END GO EXEC DisplayDate GETDATE(); ...
Saving a select count(*) value to an integer (SQL Server)
Hi everyone, I'm having some trouble with this statement, owing no doubt to my ignorance of what is returned from this select statement: declare @myInt as INT set @myInt = (select COUNT(*) from myTable as count) if(@myInt <> 0) begin print 'there's something in the table' end There are records in myTable, but when I run the code...
Get percentiles of data-set with group by month
Hello, I have a SQL table with a whole load of records that look like this: | Date | Score | + -----------+-------+ | 01/01/2010 | 4 | | 02/01/2010 | 6 | | 03/01/2010 | 10 | ... | 16/03/2010 | 2 | I'm plotting this on a chart, so I get a nice line across the graph indicating score-over-time. Lovely. Now, what...
Semi-complex aggregate select statement confusion
Alright, this problem is a little complicated, so bear with me. I have a table full of data. One of the table columns is an EntryDate. There can be multiple entries per day. However, I want to select all rows that are the latest entry on their respective days, and I want to select all the columns of said table. One of the columns is a...
help translate this week query from Oracle PL/SQL to SQL Server 2008
I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database: SELECT TRUNC( /* Midnight Sunday */ NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL) ) AS week_start, TRUNC( /* 23:59:59 Saturday */ NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1 ...
CTE and last known date processing
Input @StartDate = '01/25/2010' @EndDate = '02/06/2010' I have 2 CTEs in a stored procedure as follows: with CTE_A as ( [gives output A..Shown below] ), with CTE_B as ( Here, I want to check if @StartDate is NOT in output A then replace it with the last known date. In this case, since @startdate is less than any date in output A...
SQL Server: Difference between PARTITION BY and GROUP BY
I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it sounds a lot like GROUP BY, maybe with a little extra functionality added in? Are they ...
How do I use BCP or Sql Server Management Studio to get BLOB data out of Sql Server?
I'm sorry if this question has been asked already, but I couldn't find it anywhere. I have a table that stores files as BLOBS. The column that holds the file is an image datatype. I would like to be able to extract the binary data out of the column and turn it in to an actual file. I would ideally like to be able to do this with BCP o...
Multiple Table Joins to Improve Performance?
If I have a table structure like this: Transaction [TransID, ...] Document [DocID, TransID, ...] Signer [SignerID, ...] Signature [SigID, DocID, SignerID, ...] And the business logic is like this: Transactions can have multiple documents Documents can have multiple signatures And the same signer can have multiple signatures in mu...