We are looking for a free solution to compress our SQL Server backups for SQL Server 2005. I am aware of SQL Safe freeware edition, but I was wondering what others thought of storing backup files in compressed folders to achieve the desired result. This would allow us to use the native SQL backup tasks and native windows compression, t...
What's the best way to calculate the distance formula around a central location using a radius in miles? We've typically been using a circular radius formula, but are considering using a plain square because it's faster and much more efficient. Sometimes we'll need to be exact, so we'll use the circle, but other times we can just use ...
Our DB server fell, and we need to restore some of it's DB's. We can find only the LDF file of the needed DB. Is there a tool that can use that for restoring? We're using SQL 2005. ...
MS SQL has a convenient workaround for concatenating a column value from multiple rows into one value: SELECT col1 FROM table1 WHERE col2 = 'x' ORDER by col3 FOR XML path('') and that returns a nice recordset: XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------- <...
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'm using SQL Server 2005 and the the Dynamic Management View sys.dm_db_missing_index_details. It continues to tell me that Table1 really needs an index on ColumnX and ColumnY, but that index already exists! I've even dropped and re-created it a couple times to no avail. More specifics: The view lists Column1 under equality_columns. Co...
I have an already large table that my clients are asking for me to extend the length of the notes field. The notes field is already an NVARCHAR(1000) and I am being asked to expand it to 3000. The long term solution is to move notes out of the table and create a notes table that uses an NVARCHAR(max) field that is only joined in when n...
Is there a 4 byte unsigned int data type in MS SQL Server? Am I forced to use a bigint? Possible Duplicate: http://stackoverflow.com/questions/1509933/sql-server-4-byte-unsigned-int ...
Is there any performance gain using a CTE over a derived table? ...
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 ports or modes of communication do you need to open up for SQL Server 2005 Transactional Replication? Main and slave are geographically separated. ...
Is there a way to create new logins and set database permissions on a SQL Server 2005 using C# and SMO? If so, how? If not, what is the best way of doing that? ...
I have a table with the following columns: A B C --------- 1 10 X 1 11 X 2 15 X 3 20 Y 4 15 Y 4 20 Y I want to group the data based on the B and C columns and count the distinct values of the A column. But if there are two ore more rows where the value on the A column is the same I want to get the maximum valu...
Anyone got any insight as to select x number of non-consecutive days worth of data? Dates are standard sql datetime. So for example I'd like to select 5 most recent days worth of data, but there could be many days gap between records, so just selecting records from 5 days ago and more recent will not do. ...
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 | ...
Related to this question, I decided to check the UDFs in my data warehouse (which should largely have been deterministic), and I found several which aren't which should be. For instance: CREATE FUNCTION [udf_YearFromDataDtID] ( @DATA_DT_ID int ) RETURNS int AS BEGIN RETURN @DATA_DT_ID / 10000 END Shows up in this query: SELE...
Can I ascertain the name of the stored procedure that caused the trigger to fire from within the trigger? ...
I have a table with a TEXT column where the contents is just strings of CSV numbers. Example ",1,76,77,115," Each string can have an arbitrary number of numbers. I am trying to set up Full Text Indexing so that I can search this column rapidly. This works great. Instead of running queries with where MY_COL LIKE '%,77,%' and MY_COL LIKE...
In the tradition of this question and in light of the documentation, how does one make this function deterministic: ALTER FUNCTION [udf_DateTimeFromDataDtID] ( @DATA_DT_ID int -- In form YYYYMMDD ) RETURNS datetime WITH SCHEMABINDING AS BEGIN RETURN CONVERT(datetime, CONVERT(varchar, @DATA_DT_ID)) END Or this one (because of t...
I'm trying to get a stored procedure to work for a co-worker who is out sick (and thus can't be asked for guidance). I have a SQL Server 2005 database that has this exact procedure, and I'm trying to make the scripts to convert a test database to match this dev database. My script has several lines like: CAST(RELATIVE_ERROR_RATIO AS F...