Calculate the last day of the quarter
What's the most efficient way to calculate the last day of the prior quarter? Example: given the date 11/19/2008, I want to return 9/30/2008. Platform is SQL Server ...
sql-server
Reporting Services Interactive Sort and Paging Problem With ReportViewer
I have been having a strange problem with interactive sorting and paging within a ReportViewer that I can't figure out. I am using a local report definition and a ReportViewer control within an aspx page. I have AsynchRendering set to true. Everything seems to work fine except for this problem. Here are the steps to reproduce the pro...
How to move a sql server express mdf file to a different sql sever express server?
I'm trying to move my database.mdf file from a development environment to a SQL Server Express server that is hosted on my VPS - but I can't figure out how to attach the file to my database server. Help! ...
How would you do realtime data transfer between databases when data is updated?
Here's my problem at a high level: We have two business applications. App1 inputs and stores a large set of data. We need something that will transfer data from App1 to App2 whenever any relevent data in App1 has changed. Essentially we want the data in App2 to be synchronized from App1, except that App2 contains a subset of the data...
Sql Server Case Statement gives me errors in user-defined function
I want to use a case statement in my user defined functions because I need to match on a number of terms. I could use a table for the matches but then I wouldn't be able to put it inside the Computed Column definition. This works with IF statements: CREATE FUNCTION MaraSizeNumber ( @ms varchar ) RETURNS varchar AS BEGIN IF ms ...
Recommended framework for data aggregation
We have an application that will be collecting data and storing it in local WinXP PCs using Microsoft SQL Server Compact. We want to aggregate that data up to a single full-blown SQL Server for reporting and archival. The data transport needs to be fairly continuous (i.e. not batched) though some latency is acceptable (a minute or two ...
SQL Server 2005: "Protecting" stored procedures from FMTONLY mode used by MS Access
Some of the stored procedures we have contain conditional logic, like this: Create Procedure dbo.DoSomething(Some Parameters) As ... If (Some Condition) Begin Set @SomeVariable = SomeValue End ... Select ... When such a stored procedure is used as a recordsource for an MS Access form, and us...
Portability of SQL Server applications between 32 and 64 bit versions of SQL Server?
I have an application that is currently running against a 32-bit SQL Server 2005 Standard Edition database. For reasons I won't go into here, I need to move the database to a 64-bit SQL Server 2005 Standard edition running on 64-Bit Windows Server 2003 R2 Datacenter. Are there any migration issues I should be aware of in the Applicatio...
Is it worth the trouble to use tinyint instead of int for SqlServer lookup tables?
When designing a lookup table (enum) in SqlServer 2005, if you know the number of entries will never get very high, should you use tinyint instead of int? I'm most concerned about performance, particularly efficiency of indexes. Let's say you have these representative tables: Person ------ PersonId int (PK) PersonTypeId tinyint (FK ...
Deterministic Scalar Functions
I want to index a computed column in my database table which uses a number of user defined functions. However I just learned that my column is non-deterministic. Without indexing its going to be way to slow to run queries that I need. What's the best way of tracing through user defined functions to determine whether they are determinis...
SQL Backups in Compressed Folder
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...
help with distinct rows and data ordering
If I have records: Row Date, LocationID, Account 1 Jan 1, 2008 1 1000 2 Jan 2, 2008 1 1000 3 Jan 3, 2008 2 1001 4 Jan 3, 2008 1 1001 5 Jan 3, 2008 3 1001 6 Jan 4, 2008 3 1002 I need to get the row (date, locatinid, account) where the row has t...
What are the best practices in writing a sql stored procedure
I found that SQL stored procedures are very interesting and useful. I have written stored procedures but i want to write well crafted, good performance tuned and concise SPs for any sort of requirement and also would love to learn about any tricks or good practices for stored procedures. How do i move from the beginner to the advanced st...
Does sp_updatestats cause tables to be inaccessible in SQL Server 2005?
Does updating statistics cause tables to be inaccessible? In other words, can you run this procedure without downtime? Specifically for SQL Server 2005 ...
T-SQL distance formula using a radius in miles around a central location
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 ...
How do you return a constant from an sql statement?
How do I return a constant from an sql statement? For example how would I change the code below so "my message" would return if my (boolean expression) was true if (my boolean expression) "my message" else select top 1 name from people; I am using ms sql 2000 ...
PostgreSQL or MS SQL Server?
I'm considering using PostgreSQL with a .Net web app. Basically 3 reasons: Mature Geo Queries Small footprint + Linux Price I'm wondering a bit about tools though, SQL Server Profiler and query plans and performance monitors have been helpful. How is this world with Postgres? Some other things I should consider? Edit: Will most like...
SQLServer 2005 - Change character in NVarChar
I receive some arguments into a stored procedure. These arguments are NVARCHAR's. I have a problem, when I need to cast some of these values to FLOATS, because they are being received as e.g. @VALUE1 NVARCHAR(100) DECLARE @ChangedValue SET @ChangedValue = CAST(@Value1 AS FLOAT) E.g. @Value1 = "0,001" Gives me a problem, as it expect...
Enabling Auditing feature in SQLServer 2005
Did you ever use SQL Server auditing features on a production db? How did that impact on performances, and are there differences you noticed between different versions of SQL Server. Also how we need to enable the audit features. ...
How to choose returned column name in a SELECT FOR XML query?
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 ---------------------------------------- <...