Hi, I have a problem where I must get all records from "names2" that are not in "names1" table. both tables are identical instructure. Both have a composite primary key on three columns , First_name , middle_name , last_name. This query is proving to be troublesome due to the composite pri...more >>

I have had a cascading series of problems stemming from partitioned tables with identity columns, and it's ended at Instead Of triggers and the inserted virtual table needing to be referenced from dynamic sql. SQL Server 2000. I've got partitioned tables and a partitioned view, and each of the...more >>

local connection works just fine... but when I try remote provider and remote server with variations on the connection string I cannot get past the connection errors. At this point the MSSQLServer is local with local range IP address. Anyone with experience on the many possibilities? Thanks. ...more >>

Hi everyone, I am trying to drop PK and FK constraint that i don't know the constraint names -- the constraint names were generated by SQL Server 2000. I found the following code for dropping default constraint and changed the xtype from 'D' to 'PK but it did not work. I modified a few oth...more >>

Hi, I created a linked server so that I can make a cross server join. However, the query runs slow. Does the cross server join use any indexes or is there anyway to make the query runs faster? Thanks Ed...more >>

Hey guys Scenario --------- I'm basically pulling a load of old data periodically from a bunch of client MSDE machines onto a server, and pushing the data into flat files using a ..Net application that I knocked up. The flat files are delimited, and there are multiple files based on their...more >>

The table defintion is Channel int ' number from 1 to 8 typically, but can go much higher, up to 800 about max StartUse dateTime() EndUse dateTime() Each time a channel is used a record is created with the channel number being used, the datetime the channel use is started and when the cha...more >>

Hi, On my SQL Server 2000, I have 20 tables that I fill with hundreds of thousdand of rows of data daily by running a DTS package PKG_A. Exact table structure and data are irrelevant. Immediately after the DTS package PKG_A is done importing, I am running another DTS package PKG_B that exp...more >>

How can I modify the query listed below. To resolve the error message listed below. The error coresponds to Drive (C,D,E,F,G) field from the #drive table having more than one value. Please help me resolve this error. Error Message: Msg 512, Level 16, State 1 Subquery returned m...more >>

Dear all, I wish to urgently write a select stamenet to compute % columns of each record, based on the previous record. The ddl is posted as below - : --start of code if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblog]') and OBJECTPROPERTY(id, N'IsUserTable') =...more >>

Hi, I have a table and need to write a query to get data in a particular manner. The SQL are below CREATE TABLE Pivot ( [Year] SMALLINT, [Month] TINYINT, Amount DECIMAL(2,1) , AccId int ) INSERT INTO Pivot VALUES (1990, 1, 2.8, 1) INSERT INTO Pivot VALUES (19...more >>

Hi folks, I have a piece of legacy code as follows: UPDATE deal SET upd_code = rg_norm.hierarchy_id FROM rg_rules INNER JOIN rg_norm ON rg_rules.hierarchy_id = rg_norm.hierarchy_id INNER JOIN rg_mapping ON rg_rules.rule_id = rg_mapping.rule_id INNER JOIN deal ON (rg_rules.type_code = dea...more >>

Hi, I have a query that sends the output result into a temp table which has three col. ID int, DbDest char (10), Notes varchar(50) total raws in this table are between 10 -100 , never more then 100. Is there a way I can send that data to an email using CDOSYS stroe proc sp_send_cdosysmai...more >>

Hi All, As you can perhaps tell from the subject I have a need to frequently find out the largest id value in a data table. The table is currently a few million rows. It will grow to potentially 100 million or so before being archived. My question is, will doing frequent MAX(ID) operatio...more >>

I have a Stored Procedure which is used by a .NET DataSet to insert a value into a table: INSERT INTO [dbo].[tblScene7Items] ([Title], [Scene7ID], [Description], [ImageGroupID], [ModBy], [ModStamp]) VALUES (@Title, @Scene7ID, @Description, @ImageGroupID, @ModBy, @ModStamp); SELECT ItemI...more >>

I have read and understand the issues of using a uniqueidentifier in a database. Including most of the "must reads". Here is a specific question. I have a table, where 10 rows are inserted at a time, as a group. It's always 10 records at a time. Let's call tblReservation (as in a car...more >>

I have data in a table that's collected based on users signing in & out of an application. Rec Last Name First Name Status Category Timestamp 4 Lastname Firstname 1 0 2007-01-23 14:48:46.317 ... 5 Lastname Firstname 2 1 2007-01-24 09:09:37.087 Out to lunch again 6 Lastname Fir...more >>

Table1 contains serverall thousand rows. The concerning columns are EmployeeNumber and ManagerEmployeeNumber. I need to get all direct and indirect employees based on the manager supplied. There are potentially many levels. So if I specify manager 12345, I need to return all rows where ...more >>

How do i change a table 'tab_name' from 'file1' in 'primary' filegroup to new 'file2' in 'secondary' file group. Thanks, zomer. ...more >>

Hi I have a problem that really puzzles me. I have two tables that are to be joined and a where clause checking the data in a date column. The date column has an index. If I pass variables, like in the first example below, the execution DOES NOT use the index for the datefield. Execution t...more >>

We need to create a column in some of our tables that will be a UniqueIdentifier type. These columns will have a non-clustered index on them. For row retrieval purposes, would it more efficient to populate these columns using NewID() or NEWSEQUINTIALID() or doesn't it matter? Thanks very mu...more >>

SQL 2000, sp4 I'm investigating how a calendar table might be useful to my organization. The example I'm following did not set Martin Luther King day as a holiday. So, I borrowed a query for Thanksgiving (4th thur. in November) and modified it for the 3rd Monday in January. It works, but ...more >>

SQL Server 2005 ------------------------- Does anyone have a clue how to work around the situation where you want to have a column have a unique constraint yet allow nulls. My experience is that if you allow nulls in a column and set a unique index on the same column, any more than one null ...more >>

I have a table with 4 columns "ID" "ColA" "ColB" "ColC". One of them has the value "xxx", the others are null. My SQL is Select ID From MyTable Where ColA='xxx' or ColB='xxx' or ColC='xxx' But In addition to ID, I also want to know which column has 'xxx'. How can I modify my SQL Server 200...more >>

Hi, I have a strange situation where I need to use CASE function in WHRE clause. Here is my query SELECT b.InstrName, a.AccessType FROM InstrumentAccess a INNER JOIN InstrumentMaster b ON a.InstrId=b.InstrID WHERE a.ManagerID = 123 AND b.Status IN (CASE a.AccessType WHEN 1 THEN 1,3 ELSE ...more >>

Any one tell me How to generate Sequence numbers for a primarykey (string datatype) in SQL server 2000 My need is example Key should start with AAAA, then increments like AAAA, AAAB, AAAC.................AAAZ, AABA,,,,,,,,,,,,,,,,,,,,,,,,zzzz. can any one help in this ...more >>

This is a very simple example but it should get the idea across .. Lets say I have two tables. On one table (TableA) I have a trigger to copy the data into the other table (TableB). Now I have a requirement to have a similar trigger on TableB - to copy the information to TableA. Obvious...more >>

Hi Is there an "easy" way to check if there has been made a full backup of a database so I can do a log backup? The issue is that I have a stored proc that do a full database backup and then another script that runs logfile backups. The log file script checks if the database is in SIMPLE r...more >>

Hi All I have a table with 1000 rows of data, I need to allow the user to choose a certain number of records from each country (there is a country column) eg, Out of the 1000 records please show me 5 from the UK, 5 from German and 50 from the US. So my question is, can this be done via a...more >>

Good morning everyone I wish to write a query to average some figures, depending on how many values I have. I have a table which contains financials, over three years, however, some years do not have financials, and so therefore it would be incorrect to average the sum of three years, since...more >>

I have the following query that I'm using to update pricing from a spreadsheet to a MS SQL(1005) DB Table as we prepare to move to a new quoting system. UPDATE DT SET [BasePrice] = ST.RawPrice ,[BaseCost] = ST.RawCost from [MIPreStain].[dbo].[Products] DT inner join (Select * FROM...more >>

HI all, Being relatively new to SQL, and hearing that one should avoid cursors, what then are the alternatives to looping through a rowset making calculations based on a set of rules then updaating said rowset. Thanks Robert ...more >>

I am trying to add a paramter to a stored procedure on SQL 2000. If I add the parameter to the ASP page (using objCmd.Parameters.Append objCmd.CreateParameter (pName, pType, adParamInput, pSize, pValue)), and pass it to the stored proc wth a cmd.Execute, the stored proc does not execute and OU...more >>

I am trying to query a column in a SQL database with date/time data in it which looks exactly like this example: 05/16/2006 08:33:14 AM. I have two queries. In one query I want to return only the date half of the info. In the other query I want to return only the time half. I will be using...more >>

Hi, Is there a way I can view SQLServer processing steps while it is connecting to a linked server? I'm trying to track down a problem of sporadically not connecting to Linked Server to Access mdb. Thanks. Alan. ...more >>

Hi everybody. I need to get data from Oracle db directly to the SQL Serv 2k5 without any caching or dumping with the maximum available performance. I'd created the .net assembly which is references to the oracle.dataaccess.dll library, some system libs and Microsoft.SqlServer lib to interact ...more >>

Hi, i have problem / suggestion. I need to select all columns but one (id) because i need to make insert into other table where it will get new ID I know i can write select col2,col3,..... colx from .... i am interesting if exist something like select *[EXCLUDE id] from tablename ...more >>

Hi all. We did a restore into DB1 with "force restore over existing database" option just to see that it was the wrong DB we restored from. We thought we had an backup of DB1, but we didn't. We do have the transaction log for the operation. Is there any way we can use the transaction log to...more >>

Hi i have following example table & data create table test(loc char(10),mname varchar(25),fname varchar(25), DOD char(10)) go create table test(loc char(10),mname varchar(25),fname varchar(25), DOD char(10),status char(1)) go insert into test values ('AAA-111111','ali','jamal','10/...more >>