sql-server

SQL Server 2008 - Migrating database to new schema most efficient way?

I'm just wondering what the opinion is on the most efficient way to migrate data to new schema in SQL server 2008. The database has 7 tables and is 8gb in size. I briefly tried using a .NET app and the entity framework but this was very very slow. It will need to convert GUIDs to INTs in a few places and so the relationships need to per...

String concatenation operator in Oracle, Postgres and SQL Server

Is there a way to have a common operator for concatenation in Oracle, Postgres and SQL Server. In Oracle we uses '|', postgres uses '||' and sql server uses '+'. I've solved the problem in postgres by adding the custom operator '+' to support string concatenation. Is there a way to add the same operator in Oracle to support string con...

SQL Server 2008: Find out primary/foreign key in Table?

Does anyone know how I can see which are the primary & foreign keys in a table? EDIT: Thanks for all the responses. I was looking for a SQL Query to do that. Right now I'm playing around with writing a tool which can list me all Tables of a DB and show the columns. I'd like to display also which of the keys are primary keys. This is ho...

SQL Server SP optimal way to get multiple cursors with related data

What is the best technique for getting related records by SP using one db round-trip. I need to create Master.List structure on app level in C#. I have master-detail tables: 1. I need to search rows in detail table. 2. I need to find corresponding rows in master table. 3. I need to return two cursors: A: All corresponding rows from...

How to format the result set of parent child records?

All, I have a result set coming from several SQL Server tables, and I need to know what the best way to get the sorted records I need would be. I have the following records; a user name and module number as parent records, and then for each of these modules there are multiple sub-module child records. The problem I'm having is that the...

How do I 'subtract' sql tables?

Its not really a subtraction I'm looking for. And I know its not a union or intersection... I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents. How could I ge...

Graphing time intervals in Access

I have a table that looks like the following: StudyId StudyStartDateTime ============================ 1 01/01/2009 08:45 AM 2 01/01/2009 08:53 AM ... I'd like to return a query that contains the StudyId, as well as the ten-minute interval during which it was started: StudyId Interval ================== 1 8:40...

Select Distinct in SQL Server vs Access

I have a large query that pulls data from 3 tables. It is a SELECT DISTINCT query. I am in the process of migrating this query from Access to SQL Server. The query in Access, which uses linked tables to SQL Server, returns 920K records. The query in SQL Server (querying the same 3 tables) returns 1.1 million records. They are the sam...

Sql server management server

Can I replace Sql Server Management Studio Express with the enterprise version without deleting the express version? If so can you tell me how? ...

Internal SQL Server Error when referencing a named instance of SQL

I am trying to script some inserts from a source database to a target database, using linked servers. For the first time we are using a second named instance of SQL, and it is giving me an "Internal SQL Server Error" when I run the following code. If I remove the brackets around the named instance, I no longer get the Internal SQL Serv...

Is this stored procedure safe from sql injection?

This stored proc executes sql with parameters using sp_executesql. Is it safe from sql injection? create procedure ExecutePeopleFilter (@lastNameFilter varchar(20), @companyNameFilter varchar(20), @ageFilter int, @dateFilter datetime) as begin declare @sql varchar(4000) declare @params varchar(1000) decla...

Is it ok to use a XML column to store extra data?

If it ok and recommanded to use a xml column to store any extra data that the user interface might provide? For example, suppose an Employee table CREATE TABLE Employee ( EmployeeId int not null, Name nvarchar(300) not null, Phone varchar(30) null, Email varchar(320) null, Address nvarchar(max) null, Data xml nu...

SSIS XML Config File Location

I'm planning to use XML configuration files to run SSIS jobs on both development and production servers. I'll be using the SQL Server to store the SSIS packages. I'm wondering if there's a standard place to store the XML files. There's no need to create a project folder for the packages since they'll be stored in the database. So is ...

Checking where a table is replicated from - SQL 2000

I have a SQL 2005 database that has a couple of publications set up to a SQL 2000 database on a separate server. Some of these tables are prefixed with "company_live$TableName". However, I can see some in the SQL 2000 db that are prefixed with "company_limited$TableName". I'm just wondering where these tables come from... is there any ...

SQLServer CASE expressions - short circuit evaluation?

I am trying to optimize a query that does something like this: SELECT ... CASE WHEN (condition) THEN (expensive function call #1) ELSE (expensive function call #2) END ... The query plan shows that for even in cases where 100% of rows satisfy the WHEN clause, a substantial portion of the time is spent in calls to the resu...

How Do I Query for "Per Day" results for a given date range in MS SQL?

Sorry if this has been asked before. I tried looking through the related questions and didn't find anything that I thought was relevant. Anywho, I would like to build a query that will pull the number of rows that were created on a given date range and time frame. In other words. the date range would be for a given month, but I only wan...

Looking for open source code/utility for maintain calendar in SQL Server DB

I am looking for some (preferable vb.net) code to use in an asp.net application that will allow me/my users to setup calendars (that I store in SQL server db). I am looking for something that will make it easy to schedule recurring appts (i.e. every monday, every other week on thursday at 9AM, every month,1st and 3rd tuesday, every year...

select a list of rows from this table format based on distinct fk2 and > date?

Is it possible to select a list of rows from this table format based on distinct fk2 and > date? Table pk fk1 fk2 value date 1 1 1 100 1/1/2009 2 1 2 110 1/1/2009 3 1 2 120 5/1/2009 4 1 3 130 1/1/2009 I would like it to return rows 1,3,4 I only pass in fk1 Select * from table where fk1=1 'and the row with the latest dat...

Why is PostgreSQL Harder to Manage/Maintain then other Databases

I am very interested in postgesql because they have a uuid data type. I have done searches around SO and many people say that Postgres is harder to maintain and manage. Why is this? Is it because postgres needs more configuration, is it because postgres does not have a GUI? ...

SQL Query Where Clause for Null OR Match (only return 1)?

I have a table that has records with a structure similar to this.. ID RoleID 1 NULL 2 15 3 16 I wrote a where clause to get records like the following SELECT * from TableX WHERE (RoleID = 2 OR RoleID IS NULL) This gets me the record of "1,NULL" But if i query SELECT * from TableX WHERE (RoleID = 15 OR RoleID I...