I have a table that stores billing rates for our employees by client. Each employee can have a different billing rate for each client for a specified period. Here are the columns in the table. eid - Employee ID# cid - Client ID# startdt - start date of billing rate enddt - end date of billi...more >>

Hi is anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ? Thanks Marcin Sobolewski...more >>

Hi All, I have an application that reads data from a very slow database link (like 10 seconds per call) though what I am looking for would be of generic use for anyone who has long-running queries that are frequently repeated. I would like to be able to cache the results of a query so t...more >>

hi, If we consider the following scenerio: a school with 500 students. On average student takes [x] courses per year, and [y] tests per course. table of grades: +------------+-----------+---------+-------+ | student_id | course_id | term_id | grade | +------------+-----------+---------+...more >>

All, Oracle 9i provides a "USING" clause option for inner joins, that allows me to say: SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable for our software make use of, but we also support SQL Server. There is no USING opt...more >>

For a given table, I want to know all the columns that are included in an index. I have looked on the web and come up with this, which seems to work, but just wanted some verification. Are there any reasons why I should be using the metadata functions like OBJECT_NAME? Thanks Bruce SELEC...more >>

I know it may not be good design standards, but SQL7 can handle database names that have embedded spaces just fine, you just need to enclose the database name in square brackets. But can TEXTCOPY handle spaces in the database name? I first tried something like: TEXTCOPY /S server /U sa /P p...more >>

Hi All, I have what seems to me to be a difficult query request for a database I've inherited. I have a table that has a varchar(2000) column that is used to store system and user messages from an on-line ordering system. For some reason (I have no idea why), when the original database wa...more >>

Hi everyone Have a problem I would areally appreciate help with. I have 3 tables in a standard format for a Bookshop, eg Products Categories Categories_Products the latter allowing me to have products in multiple categories. Everthing works well except for one annoying lit...more >>

Hi I have 2 tables. The first has employee information and the second has payroll information. I need to find out people who are not in the payroll but in the employee table. Since the payroll has multiple instances i have to filter it and find out for each payroll. I don't think i have explai...more >>

Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a new view in Enterprise Manager which pulls data from a linked server's database. When I write the view... CREATE VIEW dbo.VIEW1 AS SELECT * FROM LinkedServerName.DatabaseName.dbo.TableName and I click on ...more >>

I received an error that the log in tempdb was full, but the log and data segments are set to automatically grow with no limit AND there is plenty of available space on the disk. So I don't understand why this error occurred. Does anyone have any ideas? Thanks, Tom...more >>

Hi I joined a project where 100,000 rows were added everyday. Now due to additional customers the expectation is 2 million reocrds/day ie 10 GB worth of textfiles. We have to estimate the hard disk, memory, # of CPUs etc.We will have one yearworth of data in the db. Rest will be in tapes etc. ...more >>

I am having a problem importing an Excel spreadsheet. I have a column in an Excel sheet with alphanumeric text and some of the cells are numeric. Some of the cells contain numbers like 12345.6 and when DTS is done importing it into a field that is nvarchar the results are "12345.600000000001...more >>

Hi All Im using a stored procedure on my sql server and am unsure of the syntax that i should use in it. Im pretty sure that there is a way to do what i want, but as yet i havent been able to find much info on it. Basically the procedure takes the machinename and username supplied and searches...more >>

Hi All Bit of confirmation needed really, as I've just come home and found that my PC is only running SQL 7.0, when I know the below code only works in SQL 2000 (I think!!!). What I want to do is put a particular DB in single user mode, do a DBCC repair_rebuild then put it back to how it wa...more >>

Ok! I have a little problem. Here's the thing: I have a MSSQL server. I have defined some alerts and I want sql to e-mail me when it happens. But I must configure "MAPI Profile". What MAPI profile means? I just want my server to use my ISP's smtp server to send messages. What do I need to do? ...more >>

Hi Everyone, I am doing a small database in ms access. I have two tables: One has all the information such as name, address, phone number, and etc. The second table contains the keyID of the first table and some additional information about the data in the first table( But not every record in ...more >>

Hi, only one question please, maybe it is a faq, but could be a boolean question for people that haven't time... I have My_user table, here there's a field 'nick_name' not Null and unique, is it good for primary Key or is better a field int. identity. Thanks ...more >>

Hi I am trying to figure out how to exit a cursor loop if a specified condition occurs. I have a select count(*) on a table like this select lagplats, count(*) from arsi where artnr = '1440' if that count(*) is > 1 I want to break that cursor loop and fetch the next row into the cursor...more >>

Hello, I'm relatively new to the database world so please forgive me in advance for my ignorance. I have recently been tasked at my job with finding a tool that will perform the following tasks: 1) extract schemas from a db, 2) produce a difference report between schemas from two databases, a...more >>

Our customer (of our ecommerce system) wants to be able to preserve deleted entities in the database so that they can do reporting, auditing etc. The system is quite complex where each end user can belong to multiple institutional affiliations (which can purchase on behalf of the user). The e...more >>

Hi all, I have some DTS packages that are used to import /export data to a SQL box located outside in a DMZ behind a firewall. We need to open up a port in the firewall so that the Internal Server can communicate (Execute DTS packages against) with the SQL box located outside the DMZ. How d...more >>

What are the pros and cons of the following two design methods ? (1) Using foreign keys to form a composite primary key of a child tables -- as in Example. POOR MAN'S ERD PROVIDED FOR SUMMARY OVERVIEW (Code provided below) *** Example 1 COMPOSITE FOREIGN KEY *** PK = Primary Key FK = For...more >>

It's not possible to drop tables which violate FOREIGN KEY CONSTRAINTS. SQL Server, however, is completely blase about dropping tables that are referenced by either sprocs or views. Short of writing unit tests for every stored procedure and view are there any tools to test integrity? I suspect...more >>

In SQLServer 2000, I am sure there was a way that you configure the database to log to a file each query that gets run against it. However, I cannot seen to find where to set this up in Enterprise Manager....more >>

Hi all, Thanks in advance to any contributions to my question. Im running SQLServer 2000 in a Win 2000 Server env. Background: ========== The database has many views that range from simple to complex joins on tables. The selection criteria is fixed eg. 'Where TaskTypeIdent = 2204 an...more >>

Hi Everyone This is a data base question so don't switch off thinking it is a Data Protection Act Question. I'm Looking for some general information regarding the UK Law on rights to access data within a database provided by a third party software provider. They are preventing me from OD...more >>

Hello all, I'm using SS 2000 and NT4 (and Access97 as front-end on another server) Well, probably by lack of knowledge about table locks, I don't really know where to start to present this problem. In Enterprise manager, section "Management->Current activity->Locks/Objects", we have a couple...more >>

All, I have a table with start and end dates/times in it, and would like to be able to calculate the number of hours represented, accounting for overlapping records. Note that I am looking for an answer on HOW to do this--I don't necessarily need it to be written for me (although it would n...more >>

Hi all, Being that so far I didn't get an answer to my below problem on the Oracle newsgroup, I figured it couldn't hurt to try here. While at my current job I've been working with mostly SQL Server, now I need to connect to our Oracle Financials running on HP-UX. The Oracle DBA set me up ...more >>

hola! alguno sabe como uso el top en el mysql??? debo hacer la siguiente consulta y no le gusta al mysql SELECT TOP 10 * From ControlDeCaja WHERE CodUsuario = "xxx" alguien sabe? Gracias de todas maneras!!...more >>

I don't know what the correct syntax is to do what I want with the DISTINCT function (if it's actually possible). I have a query which displays a variety of fields from a variety of tables (pretty standard). However, I only want to show records where the contents of one particular column in...more >>

Hello all!!! For a year and a half now, we have been developing different Access 2000 applications. Most of them are access frontend and access backends. Some of them have SQL server 2000 as a backend. There is one application, which consists of a frontend and 3 backend mdb's. Different compa...more >>

I am writing a paper for a computer programming college class and I need to know how many companies worldwide use SQL server? How many servers does this constitute? At what rate is SQL use growing? If you can't provide close to exact numbers, could you point me to a source where I could find the...more >>

Anybody know how many companies worldwide use SQL server and how many individual servers this amounts to? Also, at what rate is SQL use growing? Can someone at least point me to a source where I could find close to exact numbers?...more >>

I'm trying to use ASP pages generated in Frontpage to update an SQL server database. I can view information from the database but cannot update, I just get a message saying an error has occured. I am not too worried about security, I'd be happy with one login that has the rights to do anything ...more >>

I'm trying to use ASP pages generated in Frontpage to update an SQL server database. I can view information from the database but cannot update, I just get a message saying an error has occured. I am not too worried about security, I'd be happy with one login that has the rights to do anything ...more >>

I have a number of MS Access apps that link to SQL Sever tables. If the SQL Sever database is moved to another server, or the server is replaced, the unc address to the database changes as the server name has changed. All my apps fall over as the linked tables are addressed using the server nam...more >>

Hi, I have a DTS package (SQL2k, sp3) that I can to execute agains a new database in the same server. I don't see how to easily redirect. If you change the connection properties and clear the transformations you have to redo them one at a time (too long). If you don't data still goes to the...more >>

Hi; I would like to do something like to run something ling --------------------------------------------------- declare @myfield varchar(10) set @myfield = 'customer_name' update sales set invitation = (select @myfield from cust where yada = '1') where anotherfield = '22' -------------...more >>

Hello, I have a situation which would essentially use a co-related subquery. I am trying to avoid using a co-related subquery due to its slow performanc and use a join statement instead. Here is what I am trying to do: Tables: ======== Limit ------- RelID ProdID Days Amt1 Amt...more >>

I need just the names of tables, views and sprocs within a SQL Server database. What's the easiest way to do this?...more >>

Hi all We have a small database that we host on our own local server. In order to get the information into the database I have a VB program that collates the information from a variety of sources and then over our local internal network writes it to the database tables. This information is ...more >>

Hi, I am having problems getting anything useful out of the index tuning wizard. I have created a table and inserted data into it. When i run the index tuning wizard i expect 2 indexes to be recommended so the book says (Index011 with a key on the uniqueid column and a non clustered index ...more >>

Hi! I' m sitting here now for days and I still get no conclusion on my problem. I have an MS ACCESS .mdb File and I would like to export it with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you know other tools which are able to do that via command line I would appreciate any ...more >>

I currently use Dreamweaver MX, ASP, MS Access & VBScript - but apparently I need to get something more stable - I have been told that the best option would be MS SQL2000 - however, I really don't know where to start. Can anybody point me in the right direction of a "simple" step by step tu...more >>

Hi All, I have a SQL Server 7 installation running on a windows 2000 server. SQL Server switches from mixed mode authentication to Windows Only on reboot. Has anyone else experienced this? Any help would be greatly appreciated. Service Pack Reports: Microsoft SQL Server 7.00 - 7.00.1077 (I...more >>

I have a simple stored procedure that is recompiling over and over. I have run several traces and I can't figure out the problem. Here is the code: CREATE procedure dbo.Sp_multi_selectEmployee ( @EmployeeID int ) As select EmployeeID, UserName, FirstName, LastName, DealerID, Password, ...more >>

Hi I can't work with my sql server 2000, When I try to open a table I get the message: "The query cannot be executed because some files are missing or not registered. run setup again to make sure the required files are registered. I uninstall and install again (few time) But all the ti...more >>

Hello....Can someone help me please? I had a mdf file with 48 Mb...and suddenly, the size of it increased to 690 Mb (without any action or movement of data)... Why could it be done?....is there any solution for thar? Thank you. Tony...more >>

I am using SQL 2000 on a Win 2k server. I have read some where that you can fire FTP commands from within t-sql statements. Something like a stored proc that gets called by a job every night that would build a recordset and then ftp it out to another server as a txt file. I understand that ...more >>

Hi, I am facing one serious problem. As soon as i started the sql server first time after installtion, my windows 2000 machine reboots. Once the windows machine comes up, sql server does not get started event if i manually try to start it. My Machine configuration is as follows: 1. Windows 2...more >>

I was at a conference and had it asserted to me by an Oracle afficiando that Oracle and DB2 handled low-level locks "better" than SQL Server, and that this was likely the cause of SQL Server's relatively slower and more deadlock-prone performance when running the same application. (SQL does seem...more >>

can someone point me to the differences in the datatypes between access and sqlserver? I've writen a program that creates a table in access using an sql create query. However, if I point to a sqlserver odbc datasource the query bombs ...more >>

I thought I'd pass this one on - sorry slightly off topic but still it's SQL !! http://www.sqlporn.co.uk ned!...more >>

I have a (hopefully typical) problem when it comes to cube design. We store millions of product records every year, broken down by month/quarter. Each product can be assigned to various heirarchial classification groups etc. The data in an OLTP DB occupies roughly 100G for a typical year. We'...more >>

I'm trying to install SQL server 2000 inside SBS 2000. Actually I think I already did it. I just don't know how to run/start it up. I want to upgrade my SQL 6.5 database backup into 2000. I have the book SQL Server 2000 Administration by SYBEX. Here is what I need to know: The best bo...more >>

Hi, Did anyone successfully set up a local package to first ftp a db.bak and second perform an automated db restore? I need to perform an automated task, which ftp nightly backup file to another server and then restore onto a database and leave the database in read-only mode for additional ...more >>

Hi, Is length of sql query limited in MS_SQL Server ? tia pluton ...more >>

I was created a function named aa. It is possible to reference to it without using the dbo. prnounce ? eg: select aa(), not select dbo.aa() thanks, Viktor ...more >>

The purpose, as you can probably guess, is to produce a set of sample documents from a large document run. The data row has a CLUB column and an IFC column; I want a set of samples that contains at least one of each CLUB and at least one of each IFC, but no more than necessary. Example schema...more >>

Hi, I have a record set that is bound to a table in MS SQL Server. One filed in the table is bound to a "long" type member variable in the RecordSet. What will happen to the "long" variable when the field is NULL in the table? Thanks! -Yi...more >>

Hi everyone, How can I retrieve the current identity used in a procedure from a trigger. What I am trying to do is: The procedure takes a @username (nvarchar) as a parameter, and the procedure identifies the @username's identity and tries to delete the identity's row. An Instead Of trigger...more >>

I need to develop some crystal reports on some .NET ado datasets. This is easy to do if I actually had data to work with. It is much easer creating reports with you have data. Therefore, I would like to run the stored procedure that the .NET ado dataset is built on and generate an XML file. ...more >>

Sql Server 7 Standard Edition My Server was renamed and the normal steps of reinstalling and running sp_dropserver and sp_addserver were followed. Everything seems to work fine but jobs from the master server. I did defect the server as a target server since it appeared as offline anyway. ...more >>

Hi, All I use VB.NET to create a database application, I create a trigger for "Order" table. Every time a order is placed, UnitsOnStock will be reduced on "Inventory" table. If UnitesOnStock < 0, I raise a error "Not enough units on stock", but I don't know how to pass the error message t...more >>

Hi, All On Northwind database in SQL Server 2000, I try to setup default value at table design windows for "OrderDate" using GETDATE(), but it does not work. How do I use setup GETDATE default value for date type? Thanks Kai ...more >>

hi i got a query that takes about 14 mins here it is select BDProduct.ProductCode,BDProduct.ProductName,SALTerritory.TerritoryID ,SALTerritory.TerritoryName,SALAccount.AccountID,SALAccount.AccountName ,sum(SalesNetFact.Qty2) as Quantity ,sum(SalesNetFact.bonus) as Bonus from SalesN...more >>

I have a 40 MB database in excel format. I need to use it in Analysis Services, I imported the data by DTS (Data Transformation Services), everything is working I can see the database, but I can't load it in the analysis services. I have to construct a cube but I can't see the database in any ...more >>

hi I need to write a stored procedure that takes input parameters,and according to these parameters the retrieved fields in a select statement are chosen. what i need to know is how to make the fields of the select statement conditional,taking in consideration that it is more than one field ad...more >>

Hello, I have a problem with a query i'm trying to create. I have a site that provides courses and at the end of each lesson the students take a test with multiple choice answers. All that information is stored in a results table that has the following structure: Course_id (the id of the...more >>