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 >>