I have recently been assigned to take over several MSSQL environments and found some of the existing practice confusing. As most of my previous experiences are on Oracle and Unix platform so would like your inputs and comments. 1) TX log truncate: In the existing environment, there are sched...more >>

Is there a way to give a particular query greater system resources, such as share of the processor? I have two queries that were started from Query Analyzer. One is very long, the other rather short. However the long one is taking almost all the processor resources and not allowing the short...more >>

I'd appreciate any help please. I'm designing 3 tables Employee, Dependant, & Claim for insurance purposes. An Employee can have multiple Dependants and claims. (The employee owns the insurance) I thought it might be a good idea to have a claim have a foreign key to Employee.ID. This is fi...more >>

Hi I have some data that need to be filtered based on a SET of Id's. If it's about a single ID, then i would pass it as a parameter in a stored procedure and use it within the the WHERE Clause, but here those ID's are determined in run time and I can't simply create a a stored procedure for a...more >>

My application uses sqldmo to retrieve the list of sql servers in the network, it works fine in some machines, but it is crashing the application in others, no error and no exception, it shuts down the application. It fails in the following line: SQLDMO.NameList sqlServers = sqlApp.ListAvai...more >>

I've got the following query in SQL 2000: select a.SSN, a.MonthName, a.IMClinicDay, b.IMClinicDay as SecondDay from tblResidentRotations a inner join view7 b on a.SSN = b.SSN where a.AcademicYear = '2004-2005' and a.SSN = '999999999' and datename(month, a.IMClinicDateFirst) = b.MonthName ...more >>

Hi, What is the maximum character a text type column can contain ? Can't change the length to upper than 16.... What is the bigger ? Varchar(8000) or Text ? Regards ...more >>

Is it possible to set READ UNCOMMITTED to a user connecting to an SQL 2000 server instance? I understand this can be done via a front end application. But what I am looking to do is to assign this to a specific user when they login to the server via any entry application. Can this be set with ...more >>

Hallo to everybody. I have a DTS in SQL Server 2000 and I need to execute it from stored procedure. I know that there are various method fot does this but they doesn't work. The first method that I try to use is with the stored procedure "xp_cmdshell". If I write in DOS prompt ...more >>

SQL Server 2000 I have a stored procedure that uses an extended SPROC to send an email notification to my customers when a document is distributed. However, the SPROC has an unexpected side effect. If I run it in its current incarnation, it only sends one email and then exits. However, i...more >>

I am using ODBC (ODBCLink/SE) to connect to HP3000 system; Retrieving the data into Microsoft Excel goes fine using MicrosoftQuery. But if I try to use SQLServer2000-DTS on Windows2003 to do the import, it always fails and gives the message: "ODBCLINKSE does not allow multiple thread" Do...more >>

I am looking to create a constraint on a table that allows multiple nulls but all non-nulls must be unique. I found the following script http://www.windowsitpro.com/Files/09/21293/Listing_01.txt that works fine, but the following line CREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a) ...more >>

I have some problem with datatime. SELECT * FROM stat WHERE data > '2005-05-24 14:07:28' ORDER BY id Asc I got the error: Microsoft OLE DB Provider for SQL Server error '80040e07' The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ...more >>

I am debugging one of our programs and ran the fix in Test. I would like to compare table 1 between Production and Test. I want the query to output column 1 if Production <> Test output. What is the best way to achieve this? jeff -- Message posted via http://www.sqlmonster.com...more >>

hi, I am using SQL SERVER 2000. Problem that i am facing is when ever I check locks in Enterprise Manager I find following blocking - : 1) sp_cursoropen;1 2) sp_cursorclose;1 3) sp_cursorfetch;1 We dont have any control over application(source code).Whatever we have to do, has to done...more >>

All, International company in Australia needs an allround DBA (development&Admin) in MSSQL and Oracle. If you have 5+ experience in development and (not OR) Administration in both MSSQL and Oracle and like challenges, please send CV to rooty_hill2002@yahoo.com.au. I will send the positio...more >>

Hello all, I use Crystal Report v9, SQL server 2000. When I try to connect to a Database via ADO, I got the following error. Login failed. Details :ADO Error Code:0x80040e4d Source:Microsoft OLE DB Provider for SQL Server Description:Login failed for use’xxxxxxx’ SQL State: 42000 Nativ...more >>

I have tried to install MSDE 2000, first the normal version, then the one for developers using Visual Studio .NET. Each time the installation appears to complete, but there is no shortcut to start the program and it's not in the Start/Programs menu. However, if I look for it in "Add and Remove P...more >>

Hi, Can we use a parameter that is a column name in a function ? Here's my function : CREATE FUNCTION dbo.fn_counting (@colnumber varchar(2),@number varchar(1)) RETURNS int AS BEGIN DECLARE @column varchar(2) DECLARE @ColTotal int SET @column = 'R' +@colnumber (This next line WOR...more >>

Using SQL 2000... tblCustomer: CustomerID int CompanyName varchar(20) HasRetailStores bit HasWholesaleStores bit HasOtherStores bit tblInvoiceMessages: MessageID int MessageText varchar(100) CustomerID int AllRetailStores bit AllWholesaleStores bit AllOtherStores bit AllStores bit ...more >>

I am currently in the process of making changes to an application using a SQL Server database and have made changes to a development copy of the live database which includes changing and adding columns to tables and updating stored procedures. I was wondering if anyone knew of any free softwar...more >>

Hi, What is the best way to model this: Assume I have two objects: Agency and Publisher, and both have a 1-to-n relationship to Employee. This is a true 1-to-n relationship, as each Employee can only work for one Agency or one Publisher. Let's assume further that I cannot introduce a supertyp...more >>

Dear All, SQL-SERVER-DB Access interface 25 users I want to know how the users are working with the interface: * What forms the use * What routines the run ( commandbuttons) * How long they use the interface * etc. I'll use a public function to add the information. ...more >>

I'm keeping in the database a log of all the sessions for my application. I'm trying to write a stored procedure that returns all the sessions that; the login contains a certain string, loggedin after a certain datetime and loggedout before another datetime. Any combination of these parameters c...more >>

is there a difference in the previleges of 'sa' login and other login with 'sysadmin' role (and 'db_owner' for all databases) ? can they do the exact same things ? ...more >>

Hi, There is a stored procedure which runs through job. It is calling to other stored procedure and other stored procedures are calling to another .. so on (approx 12-15 sp in batch) Problem: Sometime it does not execute properly. (approx very rare... once in 500 execution or sometime on n...more >>

Hi, suppose i have 3 tables main table CREATE TABLE table1 ( [t1ID] [int] primary key , [t1Name] nvatchar(50) NOT NULL, ............................ ) and connections tables (t1ID is a foreign key in all connection tables) CREATE TABLE tbl2 ( [t1ID] [int] not null, ...more >>

Hello I have 2 sql servers in my company and many remote sites. I am trying to figure out the best way to keep them safe, since both have access to the internet behind the firewall. I was planning to disbable the default gateway on one or maybe disabling file sharing on both, i was also thinking...more >>

Hello, My sql server 2000 database is on a shared server that I have absolutely no control over. When i run query select getdate() it is reporting the wrong time! I am pretty sure that the sql admin changed the date on the server last night and now this is happening. Is this possible. Sh...more >>

Using BCP with the hints TABLOCK and ROWS_PER_BATCH, what is the best setting to give ROWS_PER_BATCH? Is it the size of the table you are importing in rows or a logical division of those rows? Thanks Richard ...more >>

Hi Everybody I am new to sqlserver 2000.I know basics of locks.but i dont know how to resolve deadlock issues.I am cofusing by reading articles with 90% information and remaining 10% missing.Can any one help me which is the good site to learn and resolve deadlocks. Note: I create deadloc...more >>

Two questions -- Why is it that some of my tables are not updateable, and some are?? And I'm told that there is no "memo" type field for SQLServer, and 8000 characters is the limit. Unless I go to blob or binary. What does that entail, and what are the consequences? Thanks! JA ...more >>

Hi, Does anyone here know whether it is possible to change the authentication method of a SQL server instance programmatically, rather than going through enterprise manager. I am using SQL-DMO (under C#) for some other things, but the documentation is so bad that I can't find out whether...more >>

Hi all... I would like to know if SQL SERVER can load a tab delimited text file. If yes, how? A search on the web did not return me the "load data" command as mysql or other. Thank you all. ...more >>

I have 2 SQL Server Express questions: 1) Can you manage a SQL Server Express installation from the regular SQL Server Enterprise Manager? I am getting a message that says "SQL Server does not exist or access is denied" when I try to register the SQL Server Express instance via Enterprise Man...more >>

Hello group! I would like to make a sort of pivot table using the raw data below, but my query wouldn't give me results in the ideal output format (please see below). Any idea how to deal with it? Thanks a million! create table a (referid varchar(255) ,app_id int ,description varchar (...more >>

I am starting an encryption project for my database and I'm performing some tests on decryption speed. A lot of my application queries use a LIKE parameter in the WHERE clause. To keep from changing my application I am performing all the work on the back-end; creating views, triggers and UDFs to...more >>

Hi, I am newbie to SQL Server. I am using SQL Server 2000. I know SQL server compiles the SQL stmt or SP and stores the plan for later use(I know its not always done. But should be done to reduce execution time) in memory somewere. I want to capute all the execution plan which are kept i...more >>

We are currently using SQL 2000 Standard with W2K Standard and the server has 3Gb of RAM. SQL is currently occupying 1.85Gb of memory which I believe is the maximum for this version of the OS. I believe I am at my limitations with my current versions of software. My questions are: 1) Is the ...more >>

Normally scheduling a job is a very elementary operation but for some hidden reason I've been unable to schedule a job which runs on a 28 day cycle, even though I have at least 16 other jobs seperately scheduled to run on a 28 day cycle with different start dates/times. My production SQL ...more >>

I'm not sure how to go about this and need some help. I've got a data extract rather than a properly structure table in SQL. It looks something like below: Name: Item: John Ball John Racket Paul Ball Jim Glove Jim Ball What sort of script can I run that will return each name once...more >>

We use SQL 2000 and our database is configured to grow automatically by 10%. Currently 96% of our database is used. At what point will the database expand - what is the trigger point? ...more >>

I have the following stored procedure in SQL 2000 and would like to diplay the database name where data is drawn from. I'm using 4 databases db1, db2, db3, db4 which all have the same table (Table1) with identical column names (Surname, GivenNames). CREATE PROCEDURE [dbo].[x_searchwildcard] @v...more >>

hi I have a backup up strategy as follows Differential - every 4 hrs(4am,8am,12pm,....) transaction - every 10 min I am using SQL SERVER 2000. At Some point of time my Differential and Transaction backup clashes at(4am,8am,...) when i Check my Entriprise Manager(Locks/process id) I ...more >>

All, What's the difference between a unique contraint and unique? sementically, if you want a column contain unique values, it is a contraint. And an index is for searching/sort. The questions are: 1. Does a unique constraint interally use unique index? 2. If Yes to #1, I DO NOT need to c...more >>

I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with a device. The definition of the table is as follows: CREATE TABLE devicedata ( device...more >>

Hi, I currently have two databases (DEV & TEST) with the same users but different owners. The TEST database is on a remote machine with 2 database users - USR1 & DBO. The DEV database is exactly the same. However, when I create procedures etc the DEV database recognises the owner as DBO and t...more >>

Hi, When I create a new database from Enterprise Manager (right click on databases > New Database) I noticed that I am not prompted to create a password or user name. Can I take it then that all databases under the same local group will have the same password and user name? I have tied to a...more >>

Hi, Is there an Upgrade path from SQl server 2000 Standard edition to Enterprise edition? DO we just have to uninstall and do a reinstall? Any help is appreciated. Thanks GG ...more >>

Hi, We are planning to create a prod environment on our Dev server, by creating a new named instance on DEV. Our prod server is running on sql enterprise edition 64bit and dev on SQl enterprise 32bit. Could we copy all the databases from prod server including Master,msdb,model onto our dev ...more >>