Is there a way to query the current DATEFOMRAT SQLServer 2005 is currently using with T-SQL? I have an application that reads pregenerated INSERT-statements and executes them against a database. To make the the data to be inserted culture independent I store datetime-values represented in the invariant culture (month/day/year...) . The ...
I am porting some queries from Access to T-SQL and those who wrote the queries used the Avg aggregate function on datetime columns. This is not supported in T-SQL and I can understand why - it doesn't make sense. What is getting averaged? So I was about to start reverse engineering what Access does when it aggregates datetime using Av...
How do I check if the timestamp date of a record is before midnight today? datediff is driving me nuts... ...
I have a very long-running stored procedure in SQL Server 2005 that I'm trying to debug, and I'm using the 'print' command to do it. The problem is, I'm only getting the messages back from SQL Server at the very end of my sproc - I'd like to be able to flush the message buffer and see these messages immediately during the sproc's runtime...
I have the following code for a UDF but it errors with the message: Msg 156, Level 15, State 1, Procedure CalendarTable, Line 39 Incorrect syntax near the keyword 'OPTION'. is it because of my WITH statement as I can run the same code fine in a stored procedure? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- =============...
What do I gain by adding a timestamp column called recordversion to a table in ms-sql? ...
OK, so Sybase (12.5.4) will let me do the following to DROP a table if it already exists: IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'a_table' AND type = 'U' ) DROP TABLE a_table GO But if I try to do the same with table creation, I always get warned that the table already exists, because it went ahead and tried...
I have a data set that is organized in the following manner: Timestamp|A0001|A0002|A0003|A0004|B0001|B0002|B0003|B0004 ... ---------+-----+-----+-----+-----+-----+-----+-----+----- 2008-1-1 | 1 | 2 | 10 | 6 | 20 | 35 | 300 | 8 2008-1-2 | 5 | 2 | 9 | 3 | 50 | 38 | 290 | 2 2008-1-4 | 7 | 7 | 11 | 0 | 30 | ...
Let's say I have an int with the value of 1. How can I convert that int to a zero padded string, such as '00000001'? ...
In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm bett...
I want to select the last 5 records from a table in SQL Server without arranging the table in ascending or descending order. ...
I need some urgent help! I put together a sample scenario of my issue and I hope its enough for someone to point me in the right direction. I have two tables Products Product Meta I need a result set of the following ...
I have seen a couple of questions related to string concatenation in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data: Lets say I have tables: userTypedTags(userID,commaSeparatedTags) 'one entry per user tags(tagID,name) And want to insert data into table userTag(userID,tag...
Learning a little about T-SQL, and thought an interesting exercise would be to generate a Mandelbrot set with it. Turns out someone already has (and recently, it appears). I'll let someone else post it as an answer, but I'm curious what optimizations can be made. Alternately, what would you do to make the code more readable? I'll sel...
I have several databases, one of which contains utility functions called from the other databases. Is there a way in the utility functions to determine which database a function is being called from? ...
I wish to find all rows in a table where one column is a substring of another column. In other words, suppose I have a table (called people) with two columns: firstname and lastname, and I want to find all people like "rob robinowitz" and "jill bajillion". Is there a way to do something like "select * from people where lastname like %f...
We used to have a search, that checks two columns for some words. Both columns must contain some words provided, so we use AND ... no doubts FULLTEXT INDEX is used on the columns. The select is more or less like this: SELECT * FROM SomeTable WHERE (CONTAINS(Column1, 'word1 OR word2') AND CONTAINS(Column2, 'word3 OR word4')) now ...
I am looking for any tool which will allow the performance testing/tuning of Sybase nested stored procedures. There are many tools around and of course Sybase's own for performance tuning and testing SQL but none of these can handle nested stored procedures (i.e. a stored proc calling another stored proc). Does anyone have/know of such a...
I'm creating a stored procedure to return search results where some of the parameters are optional. I want an "if statement" in my where clause but can't get it working. The where clause should filter by only the non-null parameters. Here's the sp ALTER PROCEDURE spVillaGet -- Add the parameters for the stored procedure here @accomod...
Hello, I have a table with almost 800,000 records and I am currently using dynamic sql to generate the query on the back end. The front end is a search page which takes about 20 parameters and depending on if a parameter was chosen, it adds an " AND ..." to the base query. I'm curious as to if dynamic sql is the right way to go ( does...