(Script at the end of this message). How do I do the following: 1. Select the required table rows based on a criteria (WHERE clause) 2. Order by *any* column 3. Rank the above results beginning with 1 to ... whatever 4. Select ONLY the values between 3 and 8 of the Rank (including). ...more >>

...more >>

Hello, I'm trying to use the Index Tuning Wizard to improve the performance of my stored procedure, but I'm having a problem. When setting up the ITW, I uncheck the option to keep all indexes to get the best possible recommendations. However, when I do this and run the wizard, I get the follo...more >>

Hi, I am giving a 'summarized' scenario of the problem I have trying to solve all day... Can really use some help :( Below are the DDLs of the culprits: CREATE TABLE [SalesFACT] ( [UniqueProdCode] [varchar] (10), [TransDate] [varchar] (10), [SaleAmt] [float], . . . ) I popul...more >>

I have got a select statement in a storedprocedure which returns such a resultset like below(I am using this result set for my cross tab report): Row_Questions Col_Questions count --------------- -------------- ------ QR1 QC1 21 QR1 ...more >>

Hi, Currently, I'm working on a SQL Server DWH. I work on a snowflake Schema. Let say that I've on product table wich is linked using a FK on a product grouping key to a product group label. If I update my product refrential, this works but ... in the real life, if a new product appears and ...more >>

Hi I have the following Query which I am trying to tune for the past 3 days but it does not seem to help me. The Query returns rows. I looked at the Query plan and added some appropriate indexes which turned the scans into seeks. However the time it takes to return the data does not come down...more >>

Hi Everyboby, I have try to use some script to import the "xls" file, below: 1> EXEC sp_addlinkedserver 'ExcelSource', 2> 'Jet4.0', 3> 'Micorosoft.Jet.OLEDB.4.0', 4> 'C:\STKsales.xls', 5> NULL, 6> 'Excel 5.0' 7> GO (1 row affected) (1 row affected) 1> EXEC sp_addlinksrvlogin 'E...more >>

I'm familier with this method: SELECT id, title FROM table ORDER BY 2 (which will sort results based on the 2nd column - title) Is it possible to do something like: SELECT id, title FROM table WHERE colum2 like '%hp%' ORDER BY 2 i.e - filtering on the WHERE clause using a numeric c...more >>

I am trying to drop a column in a table but the default constraint will not let me. Based on some searching and trail and error I have found the below works but I figure there has got to be a better way, is there? // Column name is 'Equipment' // Table name is 'Directories' // Name should r...more >>

Hi all Someone advise me about Apex SQL to develop tools on SQL Server. Does anyone know it? give opinion on it? Thanks ...more >>

Here is an Update Statement that I am attempting to run: UPDATE Jobs Set Jobs.RigID = (SELECT Rigs.RigID FROM Rigs, Jobs WHERE Jobs.CustID = Rigs.CustID AND Rigs.[Name] = 'N/A' AND Jobs.RigID IS NULL) There are two tables involved, Jobs and Rigs. When I run this statement, I get...more >>

Hi all. I need to to a lot of data migration from excel spreadsheets, access databases, and SQL Server databases into a SQL Server database. In the past I would have achieved this using a combination of linked servers and ad hoc connections using opendatasource(). However my new site is hav...more >>

Hi all, I'm using dynamic sql to build a query. One of the parts adds a WHERE clause, given the column name and value as parameters, for example: SET @query = ' SELECT * FROM table WHERE ' + @dycol + ' >= ' + @dyvalue becomes: SELECT * FROM table WHERE intcol >= 3 ...more >>

Is there an object somewhere that i can use to programatically verify the syntactic correctness of a given T-SQL statement? As of now, I am submitting the query and catch an exception which requires a trip to the DB. This might seem ok...after all, why would i bother if i wasn't going to run the ...more >>

Is there anyway to hide system tables and system items in enterprise manager so only user created ones show? ...more >>

Hi, I'm looking for an application that could allow me to compare 2 different version or evolution of the same database and generate a script to bring the old version to the new or vice-versa. We develop an application that uses a SQL Server 2000 database. Overtime, the database has evolve...more >>

Hello: I am currently involved in the creation of a .NET application that makes use of SQL Server 2000. To save the database structure, we are generating create scripts and storing them in a database project. This approach has been working quite well, however, the produced scripts do not s...more >>

I'm not an SQL pro so I'm stuggling with what I think should be relatively straightforward. In Access, I'd use a group by statement with the last function but I know I can't do that in SQL. I have a table called CUSTOMERS that includes the following fields: KEYNO, CUSTOMERID, NAME, ADDRESS, C...more >>

Hello, I need to pass a parametert to a query, when I use Ms Access I wrtite the query as follow "Select * from Client Where Id=?". what is the corresponding sintax in Microsoft Sql Server 2000? thanks ...more >>

Hello, I need to pass a parametert to a query, when I use Ms Access I wrtite the query as follow "Select * from Client Where Id=?". what is the corresponding sintax in Microsoft Sql Server 2000? thanks ...more >>

Hello, I need to pass a parameter to a query, when I use Ms Access I wrtite the query as follow "Select * from Client Where Id=?". what is the corresponding sintax in Microsoft Sql Server 2000? thanks ...more >>

Can someone tell me how to do a server side trace in SQL 7 How to see what traces are running How to stop them Etc........more >>

My team is about to start a new VB.Net project using SQL2000. During the database design phase, I am faced with determining whether to use UNIQUEIDENTIFIER or IDENTIDY (int) for Primary Keys on tables. What are the recommendations for DotNet database development? I'm leaning toward the use of...more >>

Hello, I am also having an unusual isue with an Insert statement. INSERT Into CustT (TCust) VALUES (SELECT DISTINCT CompanyName FROM Contacts1 WHERE CompanyName NOT IN (SELECT CustName from Customers)) When I run the Select statement on its own, the query executes properly. When I add the I...more >>

Hello Everyone, I keep trying to update records in a table to prepare them to be imported into another. I have two tables. One named Jobs2 and the other is Contacts1 I want to pull the CompanyName value from every one of the Contacts records where the Jobs2.ContactID = Contacts1.ContactID i...more >>

Hi when i am using =* and *= geeting the same recors. anybody can tell me the problem ...more >>

My database has three user defined types which reference varchar(n). I want to change the definition of these user defined types to nvarchar(n). I've written a query to go through the database and change all user defined types to the equivalent nvarchar(n) type. I then tried to drop the user t...more >>

Hello Everyone, I keep trying to update records in a table to prepare them to be imported into another. I have two tables. One named Jobs2 and the other is Contacts1 I want to pull the CompanyName value from every one of the Contacts records where the Jobs2.ContactID = Contacts1.ContactID i...more >>

If I do, Insert tableA select * from tableB then I think we'll have a lot of stuff going to log. so how can I set this particular database option so there will be no log for any transation? ...more >>

Hi, What are the differences of "select 2" and "return 2"? Thanks in Advance ...more >>

Hi All, From a SQL stored procedure, is there a (different) way to find out if the SQLServerAgent service is started and running? In my test, the stored procedure sp_is_sqlagent_starting (which calls the extended xp_sqlagent_is_starting procedure) does not work. This stored procedure alwa...more >>

Consider a stored procedure, say, sp_mySampleProc, which takes an NTEXT variable as input paramter (that's an XML Document). The mentioned document is as follows: <MySampleNode:Sample> <Author> Mehdi Mousavi </Author> </MySampleNode:Sample> Please pay close attention to that COLON in...more >>

Hi all, I have the data below and want to sum all the district_nm belong to that region only and display the region_nm once time only. Any help would greatly appreciate. Desire results show below. Thank you again. if exists (select * from dbo.sysobjects where id = object_id(N'[F...more >>

I need to find out how to more or less do an IF/CASE in the WHERE clause. Example SELECT FROM SampleTes WHERE UserActive = @Activ In the simplest form, by setting @Active to 0 or 1 will return either inactive or active, respectively. However, if @Active is NULL, I want ALL users, inactive an...more >>

A client has asked me whether it is possible to (i) update an Active Directory when Employee details are changed in our SQL Server database. There is also the requirement to (ii) update our database when AD records are changed. I thought that given there appears to be an OLEDB provider that th...more >>

Hi, My tempdb is completely full. I try to shrink it but it is still the same size. I can't back it up because I don't have any spare space. How can I shrink the tempdb? and not affect other databases. Thanks again for all your help. ...more >>

Hi, I am trying to create a stored procedure that will test to see if a record exists (based on id) before it inserts the record. Could someone give me an example? Thanks ...more >>

I'm about the start a new project which is a VB 6 application connection to SQL Server 2000 using ADO. My question is when I is best to use stored procedures to return a query as opposed to running the query through code using a commandtext? If I am processing a large query, I presume it's be...more >>

Hi all, We have a third-party application that runs on SQL Server. I have a table that stores detail records which I have placed a trigger on to extract pertinent information when the app updates the data. Here's my boggle (Demolition Man fans rejoice): The app performs two separate udpate...more >>

I need to create a pagination mechanisem for my asp.net pages using mssql2k. I've came across multiple postings of a good solution from Don Arsenault (http://tinyurl.com/32ft8), but I was wondering if, throughout the time, there were new ideas for (better) pagination techniques. Overall, i'm...more >>

How do set a column as auto increment data type (int) in SQL Server? I know there is one in MS Access. I saw one with no trigger, no function, nor rules. I just don't know how Thank you....more >>

Hi I'm running a DTS package as part of a data warehouse ETL process, in SQL2k on Win2k, dual processors, 2GB memory. A few of the Execute-SQL steps are taking hours to run, and I need to improve this as much as possible - i.e. server/query performance tips please!! They are queries that...more >>

Hi all, after transferring a procedure from host language to SP, I'm faced with lock and timeout errors which I couldn't resolve until now. When the procedure ran in host language, it used queries (aka recordsets) and has been quite slow. But it worked so far. Now within my SP, there are...more >>

Hi there, I came up with a similar question a few months ago, but now I am in the need of stating this and other concerns to the rest of our team (including my boss) to make everybody clear about the way we develop our system and the modifications we will need to do Any help or opinion is a...more >>

Hi Ive got this problem I have a string containing a formula like @str = '[col_1] + [col_2] /20' I use exec ('update... set ... = '+@str) to update my table, according to the formula. Now I want to check if all columns in the string are correct. Well basically I want to check if all all c...more >>

Hi, My tempdb is completed full. I try to shrink it but it is not going down. I don't have any other space to run a backup of tempdb. Is there a way to clean the tempdb without using any disk space and not affecting any other databases? Thanks again... ...more >>

i am using the following case statement that works perfectly: select sum(case @company_id when 47 then t1.feeA else t1.feeB end) as aggregate_cost .... as you can see, this will sum feeA when company_id = 47 or else it will sum feeB however, i want to sum feeA for a range of values rat...more >>

Hello, Say i have two tables; This needs to be done in a sql statement. table one id 1 2 3 4 5 6 and table two id value 1 hello 1 world 2 add 2 together the output I need from the following tables is.... New table id new value 1 hello ...more >>

I have 2 SQL Server 2000 databases - one is a production database and another one for testing purposes. They do not have any relations between tables yet. I'm going to add them by creating diagrams. Usually I refresh data in a testing database by running this (truncates all tables): sp_msforea...more >>

does somebody know how this works? should not be so difficult! Thx - Player100 ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse....more >>

QUERY # 1: ========== How would one calculate results falling between noon and 5:30PM and actually show the "PM" sign. Here's my attempt to this query, however, I failed to reach the minute cut-off for 5:30PM: select distinct datename(hh, Visitdate), datename(mi, VisitDate), visitDat...more >>

Hi all, I suppose the answer is no but let's try... Is there any way to create a trigger on a system table like sysobjects? I would like to detect and potentially prevent the creation and/or modification of database objects like table, stored procs, etc Thank You...more >>

Hi, I used CHECKSUM on the first row in syscomments to detect differences in stored procs and user defined functions between databases/servers In the past, it worked very well but I am now having problem with checksum returning different values between 2 servers. Both have the same versio...more >>

Hell Please read carefully beacuse it's hard to explain how to exactly should work. So we have 2 columns 1 is autonumber and second is normal numbers ( integers ). My job is to write a single query which will return a smallest range of given number. So for example if we give 5 and there are 10 rang...more >>

Hi, I need the table's name list with gerarchical order of my Database. How I can obtain it??? Thank for All Pippo...more >>

Hi There, we are using MS Access database. I am in testing phase of migrating access data to sql server and replicating using merge replication with client installing MSDE and using access, adp file for the front end. When i migrated table from ms access to ms sql four new coloums has been ...more >>

Hello, Announcing the release of a new web site : www.SQL-Scripts.com At www.SQL-Scripts.Com you can find a collection of SQL Scripts for many different database system. Using our search system you can find scripts that you need quickly and simply. If you have scripts that you use why not l...more >>

Hi! I have a partitioned view as follows: View: recp_group partition_id (partitioning field, contstraint on this field) group_id recipient_id I am tryig to delete some rows from the view based on group_id. Since I cannot join the partitioned view with itself in a delete operation, I am...more >>

Hi all, I am using a trigger to do a transaction log - keep tracking who add/update/delete data. The problem that I have is: I use [system_user] from SQL-SERVER to log user id but in my application (VB) I use a generic account to login SQL-SERVER. So if I have 10 clients login the SQL- S...more >>

Hi all, I am having a strange issue. I am cleaning up some of my databases. I created a new database with empty tables in it and I have created scripts to insert the data from one DB to this new empty DB. In one of my tables there is a notes field that is Text(16) The very large records...more >>

Hello Everyone Does anybody know where to find more detailed documentation about SRV_DESCRIBE? If it fails it just gives error 0, it does not give enough information why it fails ...more >>

Bear with me I hope I am able to explain this correctly... I am trying to create a new view that would include a join/lookup that would perform a select on all the record sets in an associated table...but for display purposes I want all the associates records displayed in one blobbed memo...more >>

h Thanx Bharath But text data type can not be used for local variables and if used as data type for formal param, It does not allow to assign values to formal params of SP Well, I am tryin it with bcp utility Than ...more >>

I 've have a stored procedure that compares fields across databases. In order to do so it requires 2 values it acquires from 2 tables. The search is based on the ID of the data owner and a subject: proc_evaluate_results @StudentId = '222222', Course = 'PSY101' In order to obtain those values...more >>

Hi all I have been looking in to using this addon, and it seems like it will do just what we need. The problem is, im having some serious trouble getting it to work I have got the dll (for 2k - dll version 1.1.8.0). We have a slightly different install path for some reason. So i have copied the ...more >>

How do I store characters of length over 8000 in variable , Later I want to write it in file....more >>

Hi, I need to output a SMALLMONEY value using an arbitrary language, e.g. having a comma or a dot as decimal separator. Is there a way to tell CAST and CONVERT which language to use when converting a float value into a string? TIA, Axel Dahmen ...more >>

H I have the following query that selects x amount of records from my SQL 6.5 database in under 2 seconds SELECT * FROM Customer WHERE DOB>= DateAdd(year,-10, '20 Apr 2004' However if I change the code to use the following it takes a couple of minutes DECLARE @FromDOB DateTim SELECT @FromDO...more >>

Is it possible to use variable in TOP clause...more >>

I try to restore a DB every 2 minute from a backup log file. I've got a stand by server The primary server backup the transaction log every 2 minutes, the stand by server restore the transaction log every two minutes. The job tha backup the log, when finished, call an RPC to the stanby server that ...more >>