I am looking for a way to select the top 5 rows in a table (based on date), however, I want to skip the first row. The query "SELECT TOP 5 * FROM News ORDER BY CreateDate DESC" would give me the top five, but I don't want the first row in that query. The best way to describe this is to use...more >>

I am trying to transfer data from Visual Foxpro Table Version 7.0 to Sql Server 2000 through Sql Server Query:- Insert into mySqlTable Select DCode,PurDate,Amount from OpenDataSource('VFPOLEDB.1','Data Source=D:\FAST-DTML;')...DC_DASET But following Error occured. OLE DB provider '...more >>

As a data modeler and a trainer I always try to pick a domain that all my students can relate to. So I thought "The TV Guide" ! Let's model the TV Guide using ORM or ERD. Most students choose ORM because of the automatic normalzation. But try verbalizing the TV Guide. Gets non-trivial real fast....more >>

Hello, I am trying to limit the data user access through views, and I need to display different data dependent on their user name: CREATE VIEW dbo.testv1 AS if (select user_name()) like '%-%' begin select * from customers where custid > 1000 end else begin select * from custome...more >>

I have the following incoming data file with the following structure: Material Year Month Stock on Hand ------------------------------------------------------------------------------- 000000000103063768 2004 03 2.0 000000000103063768 2004 ...more >>

Hello! I'm trying to use EXEC ('SELECT * FROM Categories') into a Stored Procedure, but the users get permission denied on object 'Categories' If I'm using SELECT * FROM Categories everything works. The user haven't any permission of Categories, but the SP should have it. Is the E...more >>

Hi all, I have an old table (named Old) and a new table (named New) For every record in Old where (Old.newkey IS NULL) I want to perform the following: 1) replicate the entire row into New 2) retrieve the @@IDENTITY (after insert to New) 3) update this identity value inside Old.newkey I...more >>

Hi, I'm having trouble getting the results I need from a query. I have two tables - Customers and Orders. I need to get a list of customers in California that have placed at least one order, but none in the last 12 months, and also the date of the last order that they placed. Here are the mai...more >>

rHi, I have product table with column product id and other single atrribute for this product , also I have schedule table, each product has mutiple schedules, also I have customation table, each product has multiple customations. I would like to have a query that give me the information abou...more >>

What are page levels? how do we determine how many levels are there ? Thanks ...more >>

I'm trying to implement something simple in SQL Server and the results are driving me nuts. I have two tables, one that stores current information and one that is an audit trail of anything deleted or updated in the current table. For example, if I have a row in current and it is updated, I ...more >>

Anyone know how two join muliple records into 1. I have 3 tables like the Pubs DB. Authors Titles TitleAuthors This allows for many to many relationships. I need to be able to query this database so I can import titles & authors to a system that does not deal with many to many relatio...more >>

The following table contains vendor quote information. There can be several vendors for any given part number, with differing values in the quoEstimatedCost column. How would I write a query to extract the quoVendorID that has the lowest quoEstimatedCost for every part number (the results will b...more >>

-- Thanks ! ...more >>

I've declared a field called costpernight as a money. I now want to run a query which will return the len of the value after the decimal places. For example:- 123.45 should return 2 123.456 should return 3 12345.67 should return 2 12345.6789 should return 4 What is the best way to ret...more >>

I like to be a "good" SQL programmer and use set-based solutions where possible (because I recognize they are usually faster than cursor-based solutions), but I'm wondering if I'm barking into the wind trying to come up with a set-based solution to this problem, when the cursor-based solution is ...more >>

Hi Freinds, I get responsibility to clean up 10 SQL servers, where each one has 20-30 databases My question is that how can I find out the activity on each database. I want to start from those which are dead databases. Thanks in advance, Pat ...more >>

Hi Friends, I need to run a SP ( which is the profiler and records the SQL server) when ever the server or better to say SQL server agent restarted. Where and how should I do that? Thanks in advance, Pat ...more >>

Question as title. Thanks. ...more >>

Hi all I have these two queries. ONE SELECT tbl_BTP_Scenario.ScenarioID, tbl_BTP_Scenario.ScenarioOrder, tbl_BTP_Scenario.ScenarioName, tbl_BTP_Scenario.ScenarioDescription FROM tbl_BTP_Scenario Where tbl_BTP_Scenario.BTP_ID = 19 ORDER BY tbl_BTP_Scenario.ScenarioOrder TWO SELECT ...more >>

Looking for a query that will return all PK's for all tables in a DB. What I really am trying to do is build a drop script for just the PK's....more >>

I have a large database of approximately 120 stored procedures and a couple hundred tables. I am in the planning stages of internationalizing our product. I understand that I need to change all text and varchar, etc to nvarchar, etc. I can visualize in my mind code needed to loop through ...more >>

I am trying to set up replication and my server is registered as local and to configure replication it is telling me to delete the registration and re-register it using the server name. My question is will this affect any databases? Is this ok for a production system....more >>

Hi. I have a table with a poorly chosen definition. (Well it's my own fault - but anyway!!) There is an image field, but there is only stored text in it. Now I need to create script to change the database structure, and I want to convert it to text but I can't. I tried experimenting and ...more >>

Hi, I am working on one of the database which has about 100 views. The reason is because some views are based on other views. I am wondering if it is a good idea to put some views into one complex views to reduce the number of views. But what I concern about is some users/developers ...more >>

Hi all, I have 3 dates in a table, and I want to return the only most recent date of the three for each row. For example, if I have the following 4 columns in a table... RowID (smallint) Date1 (smalldatetime) Date2 (smalldatetime) Date3 (smalldatetime) ....how do I contruct a SELECT ...more >>

Friends, My hardware sends me a date format like this 15:10:20.99 UTC Mon Oct 18 2004 I need to process this format and parse to a valid SQL datetime format, how can I do it more easily? My front-end language is C# -Hari ...more >>

I am using asp.net and sql 2000. In my program I am doing a lot of data manipulations and before I do this I would like to copy my table to another table, just in case I need to put the data back to its original state. I would like to do this in the asp.net program. Can anyone help? Thank...more >>

update if_stationterritory set territoryid = (select fleetpoint from if_owa1 where cast(substring(ltrim(if_sitecode),1,4) as int) = stationid) stationid is integer if_sitecode is char(20) error message: Server: Msg 245, Level 16, State 1, Line 3 Syntax error converting the varchar value ...more >>

Hi folks -- Simple question, but obviously beyond me. How do you preserve the primary key of a table when moving it from one database to another? First attempt -- DTS. No luck. When I checked the table in the destination database, the primary key was gone. Second attempt -- SELECT * ...more >>

The design of my database is such that only the stored procedures are aware of the implementation of the database and it's individual tables. The client program that calls these stored procedures only sees one table that is the result of the many joins that are done. An example of this using the...more >>

I frequently need to update several interdependent tables at once and would like to find a set-based solution. Is there a way to do this sort of operation without a loop or cursor? Thanks, Eric -- EXAMPLE CREATE TABLE [dbo].[a] ( [aId] [int] IDENTITY (1, 1) NOT NULL , [val] [varch...more >>

i have a SQL Server 2000 database. I have created a table USER an insert some data in it. When I try to run the query select * from user in query anlayzer, I got his error message : Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'user'. it only works if i chan...more >>

I have two seperate databases with the same table names but different columns. The databases are linked together. Below is an example of what I'm working with starting with the database names and underneath the different columns which will need to match each other. Sales Database : orders(t...more >>

I'd like to use some local variables in a view, but SQL Server doesn't seem to like declare statements or set statements in views. Query analyzer doesn't seem to mind the query and the same query works as a stored procedure. Am I missing something? Thanks, Raul...more >>

Hi, We are having a lot of problems when killing a spid. The spid stays in the rollback/killed mode and holds its locks until we shutdown the engine. When trying to kill it again we always get SPID 62: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time ...more >>

I have created and tested two triggers directly on the local server and both work as they should. I initially had a problem with xp_smtp_sendmail that was returning a 500.1 Internal server permissions error, I corrected this with applying the correct permissions. Both triggers work when acti...more >>

I have tried the following code in order to pass a variable to the USE [@DBName] but it gives errors that it does not reconize the @DBName variable, it things the @DBName is the actual database name. DECLARE @DBName VarChar(254) SET @DBName= 'MyDB' USE [@DBName] Thank you Peace in...more >>

I've been trying to write a custom replication resolver in C# but I don't seem to have all the pieces. When I use regasm.exe to register the resolver shows up when I use sp_enumcustomresolvers. When a conflict occurs I get an error message "The process could not initialize 'My Custom Resolver' ...more >>

just wondering what is the better way of doing this query - basically i need to sum 4 different amounts based on the account_id --use a case statement SELECT @mySum32 = IsNull(SUM(CASE account_id WHEN 32 THEN amount ELSE 0 END), 0), @mySum28 = IsNull(SUM(CASE account_id WHEN 28 THEN am...more >>

I have a clients table, a payment receipt table and a payment promises table. I need to select all clients that have a late payment, this would mean selecting the most recent payment receipt date, max(receiptdate), making sure they don't have a recent payment promise, max(paymentpromise) an...more >>

Strictly speaking this is a question for the vb.net NG, but I've had no luck there and thought I'd ask here: I want to provide a query cancel feature to a WinForms applilcation. What is the best method is for doing this when you take any server-side effects into consideration? I want to do it the...more >>

Hi, When trying to kill a process the process never seems to die. When executing the kill command again we get this message: SPID 62: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. which hangs around for hours. We eventually h...more >>

I need to create a start date for a query and the date range needs to be the past 24 months. To make things a little more complex I need the start date to be the first day of the current month with the year being two years prior to the current year. The following methodology works but I'm su...more >>

Can I loop through a select statement with t-sql, grab a value at a time and then use it in a stored procedure? ...more >>

Hello I have a table that one of its colunm is TEXT data type (very big length characters). When I try to use (by selecting) the TEXT in database procedure I get the next error: "The text, ntext, and image data types are invalid for local variables." My procedure: .. declare @idoc int ,@...more >>

Hi all, I receive the above mentioned error message for an easy select: select SYDATE from dual We use Excel 2000-SP3 thx bert...more >>

Given a stored procedure written by us, how can we get a list of what other stored procedures in the same database call it? -- Joe Palm Senior Technical Developer Madison, WI...more >>

Hey all, I'm just going through my data model for a database I created and trying to ensure that it is optimized for the types of queries I'm going to be running against it. But even after reading about 10 articles I'm still not 100% certain of when to apply a clustered index, or an unclust...more >>

Hi everybody, Does anyone knows if it has an interest to perform a backup of a file or a files group in differential mode ? In fact when I perform a full backup or a differential backup on a file or a files group, both backup has the same size, so I don't see the interest of differential ba...more >>

When I execute sp_databases and bind the results to a datagrid I get three fields returned: "Database_Name", "Database_Size", and "REMARKS". The remarks field is always (null). Where/How can the value of that REMARKS field be set? Wayne ...more >>

Is it possible to run a lot of OSQL statement and write to the same outout fil?...more >>

Hi everbody, anyone can help me? when i type select * from table1 then i get the following ----------------------------------------------------- Ticker col2 col3 col4 col5 ---------------------------------------------------- BRK.A 2003-05-02 70400.00 0.00 78500.00 FARM 2003...more >>

Hello Gurus, I have a very disturbing problem. I have the following very simplified version of the table.. Equipments Client_ID int, Store_ID varchar(30) . . . some other fields I have a stored procedure to add data to this table declared as follow spEquipAdd (...more >>

Hi, i would like to have the weeknumber of a certaindate. myDate -------------- 2004-01-01 2004-01-02 ..... 2004-12-31 So i use: WEEK(myDate) But the week function prsumes that a week starts on sunday, but i would have it to start on monday. How can i tell the function that it...more >>

Hi, How do I create a query that will return a set of records where a particular field value is the sum of values for all previous records in the recordset. eg I have a table that holds values like Month Cost ------- ------- 1 1000 2 500 3 1000 4 ...more >>

Hello, I have a stored procedure and I want to access tables in different databases like this: DECLARE @test int DECLARE @test2 int SELECT @test=test FROM dbo.database1.mytable SELECT @test2=test FROM dbo.database2.mytable This is not possible. I get an error message. How can I...more >>