Ok this is going to sound strange, but I have inherited an app that is an Access front end with a SQL Server backend. I am in the process of writing a new front end for it, but... we need to continue using the access front end for a while even after we deploy my new front end for reasons I won't go into. So both the existing Access app...
Hi.. I have 3 tables similar to the sctructure below CREATE TABLE [dbo].[EmpBasic]( [EmpID] [int] IDENTITY(1,1) NOT NULL Primary Key, [Name] [varchar](50), [Address] [varchar](50) ) CREATE TABLE [dbo].[EmpProject]( [EmpID] [int] NOT NULL primary key, // referencing column with EmpBasic ...
Sometimes I keep multiple instances of SQL Server Management Studio 2005 open. I might have the dev database open in one, and the production database open in another. These appear in the Windows task bar with the text "Microsoft SQL Serve...", which means it's impossible to differentiate between them unless I open the window and scroll t...
Is it possible to make a join in SQL server that joins each row from table A to n random rows in another? For example, say I have a Customer table, a Product table and an Order table. I want to join each customer to 5 random products and insert these rows into the order table. (And each customer should be joined to 5 random rows of his o...
I have two tables Publishers and Campaigns, both have similar many-to-many relationships with Countries,Regions,Languages and Categories. more info Publisher2Categories has publisherID and categoryID which are foreign keys to publisherID in Publishers and categoryID in Categories which are identity columns. On other side i have Campai...
I want to leave sql profiler running all the time logging any queriy that runs for longer than 5 seconds. It's simple enough to start this using the profiler tool on my PC but how do I create it so it runs in the background on the server? ...
I am working on a new Datawarehouse trying to import a number of different format files from a number of different providers. The filenames may be the same each month, such as MonthlyReturns.xls/.csv, or a pattern, such as NorthWestSalesData20100101.csv). We can't ask the providers to change their naming convention. Do we have to crea...
Hi all, Quick note So, as I was writing the problem below I found a way to fix it myself. I thought I'd still post the question because: Someone might find it useful. I don't understand too much why it works. Anyway the fixed code is at the very bottom. I originally wrote: I've spent ages googling this and can find lots of re...
We're in the middle of migrating from a windows & SQL 2000 box to a Virtualised Win & SQL 2k8 box The VMWare box is on a different site, with better hardware, connectivity etc... The old(current) physical machine is still in constant use - I've taken a backup of the DB on this machine, which is 21GB Transfering this to our virtual mach...
I have a folder where files are going to be dropped for importing into my data warehouse. \\server\share\loading_area I have the following (inherited) code that uses xp_cmdshell shivers to call out to the command shell to run the DIR command and insert the resulting filenames into a table in SQL Server. I would like to 'go native' an...
Basically I want to do this: delete top( 100 ) from table order by id asc but MS SQL doesn't allow order in this position The common solution seems to be this: DELETE table WHERE id IN(SELECT TOP (100) id FROM table ORDER BY id asc) But I also found this method here: delete table from (select top (100) * from table order by id as...
IMPORTANT NOTE: I recieved many answers and I thank you all. But all the answers are more comments than answers. My question is related on the number of roundtrips per RDBMS. An experienced person told me that MySQL has less roundtrips than Firebird. I would like that the answer stays in the same area. I agree that this is not the first ...
Is there any way to upload a file to a varbinary with SQL Management Studio without writting a manual SQL query ? ...
I have a SQL Table with a column called FullName which contains, for example, "John Smith". How can order the data by the last name that appears in the FullName column? For a long name like "Laurence John Fishburne", I would like to order the data by the word "Fishburne". Thus, names are stored in the order First Name Middle Names L...
How i could check if i have connection to an SQL Server with the Connection String known in C#? ...
My job is the maintain one application which heavy use SQL server (MSSQL2005). Until now middle server stores TSQL codes in XML and send dynamic TSQL queries without using stored procs. As I am able change those XML queries I want to migrate most of my queries to stored procs. Question is folowing: Most of my queries have same Where con...
Hi: I need to return rows if exists, if not return which one of the passed value is NOT EXISTS: DECLARE @INPUT1 BIGINT DECLARE @INPUT2 BIGINT DECLARE @INPUT3 BIGINT SELECT e.Name, d.Name, c.Name FROM Employee e JOIN Department d ON e.DeptID = d.DeptID JOIN City c ON e.CityID = c.CityID WHERE e.EmpID = @INPUT1 AND d.Dept...
We have a database setup that consists of two parts: a static structure, and dynamic additions. For each database, the dynamic can be different, and sometimes we don't have data for all the dynamic fields. Rigt now, we check for empties by looking at the total count of records in the entire table, but we want to move to a more refined...
Hi, I am writing stored procs for a new system in SQL Server 2008 but I need to also update data in an older db - SQL Server 2000. I have searched but haven't found any solution to this. Is it possible? What are my choices? Thank you!! ...
I have a table which has a bunch of columns but the two relevant ones are: Due_Amount MONEY Bounced_Due_Amount MONEY I have a SQL query like the following SELECT * FROM table WHERE (Due_Amount > 0 OR Bounced_Due_Amount > 0) Would the best index to put on this table for SQL Server 2008 be an index which includes both columns in th...