Duplicate result
I am writing a query in SQL server2005. This is returning me a duplicate rows in the result. Can i eliminate this duplication with a particular column as the key? ...
sql-server
Looking for a better reporting solution than SSRS
I find SSRS produces typically ugly reports and the .rdl usually ends up being a pretty complex beast, hard to tweak and maintain. Do you know a better toolkit? It doesn't necessarily have to do it all by itself. It could be split in parts: Chart generating library Listing generating library Form generating library Plus some custom ...
Releasing a database build consistently via isql
I am releasing a database build to SQL Server 2000 via a batch file using isql. The batch file is used so multiple files are released consistently to different SQL Servers (development, test, live). The SQL Server uses ANSI code page 1252 (from sp_helpsort) but isql is an OEM client using code page 437. This means that all extended char...
How do I alter the precision of a decimal column in Sql Server?
Is there a way to alter the precision of an existing decimal column in Sql Server? ...
Stored Procedure Syntax
My stored procedure is called as below from an SQL instegartion package within SQL Server 2005 EXEC ? = Validation.PopulateFaultsFileDetails ? , 0 Though i'm not sure what the ? means ...
Is there an alternative to the SQL Profiler for SQL Server 2000
I am trying to optimize some stored procedures on a SQL Server 2000 database and when I try to use SQL Profiler I get an error message "In order to run a trace against SQL Server you have to be a member of sysadmin fixed server role.". It seems that only members of the sysadmin role can run traces on the server (something that was fixed ...
Optimising a SELECT query that runs slow on Oracle which runs quickly on SQL Server
I'm trying to run the following SQL statement in Oracle, and it takes ages to run: SELECT orderID FROM tasks WHERE orderID NOT IN (SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL AND engineer2 IS NOT NULL) If I run just the sub-part that is in the IN clause, that runs very quickly in Oracle, i.e. SELECT DISTINCT orde...
One or more files do not match the primary file of the database (error 5173).
I got this error when I checked out my database from source control. It might sounds weird to check in the sql server database, but this was what I have done because this is just a personal project. Anyone knows how to fix this? ...
SQL Server - Partitioned Tables vs. Clustered Index?
Let's assume you have one massive table with three columns as shown below: [id] INT NOT NULL, [date] SMALLDATETIME NOT NULL, [sales] FLOAT NULL Also assume you are limited to one physical disk and one filegroup (PRIMARY). You expect this table to hold sales for 10,000,000+ ids, across 100's of dates (easily 1B+ records). As with m...
ADO.NET Entity Framework and identity columns
Is the Entity Framework aware of identity columns ? I am using sql server 2005 express edition and have several tables where the primary key is an identity column, when I use these tables to create an entity model and use the model in conjunction with an entity datasource bound to a formview in order to create a new entity I am asked to ...
Hidden Features of SQL Server
What are some hidden features of SQL Server? For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough? Answers Thanks to everybody for all the great answers! Stored Procedures sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up) sp_ms...
Simplest way to create a date that is the first day of the month, given another date
In SQL Server what is the simplest/cleanest way to make a datetime representing the first of the month based on another datetime? eg I have a variable or column with 3-Mar-2005 14:23 and I want to get 1-Mar-2005 00:00 (as a datetime, not as varchar) ...
Best practices for SQL Server development across differing versions
Expanding on this question, what is the best way to develop against both SQL Server 2005 and SQL Server 2008? I'd like to see if I could just use Orcas technology on my current Vista 64 machine and since SQL Server 2005 wants to install a stub version of Visual Studio 2005, I'd like to avoid using it. However, most places where my techn...
SQL Server 2005: Importing data from SQL Server 2000
In SQL Server 2000, you have the "All Tasks... - Export Data" option. Where is this option the SQL Server 2005 Management Studio? Or, is there a SQL Server 2005 way of doing this? EDIT: I am using the Express edition. EDIT: Joel's response answers my question but Mike's answer gives a great alternative to those of us using the Ex...
Most efficient T-SQL way to pad a varchar on the left to a certain length?
As compared to say: REPLICATE(@padchar, @len - LEN(@str)) + @str ...
Sql query to determine status?
I have a table in a MSSQL database that looks like this: Timestamp (datetime) Message (varchar(20)) Once a day, a particular process inserts the current time and the message 'Started' when it starts. When it is finished it inserts the current time and the message 'Finished'. What is a good query or set of statements that, given a par...
How do you get the DataSource that a report uses in SQL Server Reporting Services 2005
In order to create the proper queries I need to be able to run a query against the same datasource that the report is using. How do I get that information programatically? Preferably the connection string or pieces of data used to build the connection string. ...
Insert into ... Select *, how to ignore identity?
I have a temp table with the exact structure of a concrete table T. It was created like this: select top 0 * into #tmp from T After processing and filling in content into #tmp, I want to copy the content back to T like this: insert into T select * from #tmp This is okay as long as T doesn't have identity column, but in my case it does....
What is a simple command line program or script to backup SQL server databases?
I've been too lax with performing DB backups on our internal servers. Is there a simple command line program that I can use to backup certain databases in SQL Server 2005? Or is there a simple VBScript? ...
How to return multiple values in one column (T-SQL)?
I have [UserAliases] (UserId, Alias) table with multiple Aliases per user. I need to query it and return all aliases for a given user, the trick is to return them all in one column. Example: UserId/Alias 1/MrX 1/MrY 1/MrA 2/Abc 2/Xyz I want the query result in the following format: UserId/Alias 1/ MrX, MrY, MrA 2/ Abc, Xyz Thank you...