We've got this whole mess trying to export data from Lotus Notes to Great Plains. As part of the process, we need to ensure that a Notes record is exported only once, but have the ability to re-export it if something breaks. So, for that reason (and some others) I decided to use a simple log tab...more >>

I have two Windows 2003 servers that both have SQL 2000 on them with Service Pack 4 installed. If I run the stored procedure sp_ActiveDirectory_Start on one, it runs with no problem. However, on the other, if I run it, I receive the error message xpadsi.exe failed. Cannot drop the table 'msdb.db...more >>

Being new to the SQL server world I have a beginner question. How do I grant access to database from a networked computer? I have tried: sp_grantlogin '\\computername\username' and I get the error: Windows NT or user <above> not found. Please check name. Any help would be appreciated. Th...more >>

I've got thousands of XML docs and have to import those to the DB. And I am having problem with getting some values because of tricky XML format. I didn't create this XML format and I don't like this format, OOP snob did. And I(DBA) have to look after their mess. I've got XML doc like this : ...more >>

Hi all, In MS SQL Management Console I can right-click on any Table and I have the option All Tasks > Export Data where I can export the table to Excel. In a View however this isn't there. I have many views I want to simply export to Excel, but the only way I've found to do it is creating a...more >>

Hi all, I'm trying to create a layout of our website for Marketing to review, and though I know how I want it presented, I'm not sure how to write the SQL code to make it work. Here's a sample of the View I've written with all our content: Level Title ID ParentID 1 ...more >>

How does one find out what type of licensing is employed on a SQL Server Box. i.e is it Per Process or Per CAL? Martyn ...more >>

Hi to all, excuse my english ( i'm an italian student... ) I have the necessity of make a stored procedure that convert one parameter passed from base64 to binary before store in a field image. Is possible? How? I have this necessity because a lot of page made in ASP get the binary ...more >>

Hi all, is there any tool which is capable to convert query initially written for msaccess database to query for mssql server. I have tons of queries which contains iif, trim and similar functions which should be converted to case, ltrim(rtrim(, etc. etc. Does anyone know for tool which cou...more >>

Using some VB sample code on the Internet I have the following that works well and exports a set of records to an XML file: Dim oCmd, sSQL, oDom Set oDom = CreateObject("Msxml2.DOMDocument.4.0") Set oCmd = CreateObject("ADODB.Command") oCmd.ActiveConnection = "Provider=SQLOL...more >>

Hello, Can someone please help me with a query? The table looks like this: BookedRooms =========== CustomerID RoomID BookDateID 1 1 20050701 1 1 20050702 1 1 20050703 1 1 20050709 1 ...more >>

There must be a way to convert the binarydata field in the SQL trace output to text data when the event type is a show plan. SQL Profiler does it but how can it be done from an imported table? Danny ...more >>

Hi all, met with something very scary and want to confirm if anyone else have met this possible bug with EM? Got a production DB, say DB_A with quite a few users in it. this day, when I look into the EM-->management-->Current Activity-->Locks/Object, I saw a lot of locking on some objects belo...more >>

I have a query : Exec 'Select * From Receiving Where Code In (' + @pCode + ')' @pCode will contain more than one string parameter, eg : A1, A2, A3 How can i write that parameters, I try use : set @pCode='A1','A2','A3' but get an error : Incorrect syntax near ',' Please help me Than...more >>

Using SQL2000. How do I format my select statement to choose one out of 24 different tables? Each table is slightly different and I was hoping I could use one select statement and format it on-the-fly instead of using 24 different ones. I had in mind using a case statement, something like thi...more >>

Here is sample data I'm working with: ID ShiftDate SegTime 99 5/2/2005 5/2/2005 1:00:00 PM 99 5/2/2005 5/2/2005 1:04:00 PM 99 5/2/2005 5/2/2005 1:43:00 PM 99 5/2/2005 5/2/2005 2:15:00 PM 99 5/2/2005 5/2/2005 2:30:00 PM 99 5/2/2005 5/2/2005 5:00:00 PM...more >>

Hi All I'm really stuck on this one so would appreciate any help you can give. In essence, I have 1 SQL 2000 table with rows of data logging stock movement. To differenciate between a stock sale and a stock receipt the table has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equal...more >>

Is there any reason why I shouldn't cause an arithmetic error(say by dividing by zero) in a User Defined Function for a situation where in a stored procedure you would use RAISERROR or in code you would throw an exception? In most situations, I can check the return value of the UDF to see that...more >>

Hello, I am looking for basic info on installing and using MSDE. I am trying to install and play with MSDE on my PC (Win2000). I finally managed to work my way through installation, and named instances but I am finding no information on how to simply START the database engine and get to a ...more >>

It is possible to drop the table without dropping the view referencing it. How do I force integrity? Madhivanan ...more >>

Hi, I can't make my mind up what the most efficient query is for the following problem. My table contains records similar to this example: ID CAT 1 A 1 B 2 A 3 A 3 B I now want to extract all ids that have the CAT value 'A' and 'B' . In this example the result would be 1...more >>

Hello, I have an Access DB with a column [date] holding dates from present day back to around year 1702, and I am trying to run DTS Import with this MDB, which fails due to a conversion error. MSDN says the datetime data type only allows 1753-01-01 to 1900-01-01, thus I am under the impress...more >>

How can I create a trigger that obliges UPPERCASE of a field in the database? thanks ...more >>

Hi, I'm trying to count field login totals for users which updates another field in another table. Which is the most efficient method? I don't want to use a standard query as it will take too long if there are 1000 users per company each with 1000 plus logins. I was thinking in terms of ...more >>

This is a simplification of a query I'm attempting to write at the moment. I can do it with a temp table, but I think it can be done in one query. 15 minutes of fame to the correct answer. 3 tables: Table Continent ================Columns================== Columns: ID (primary key) Name ...more >>

Sometimes I get error : "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. " If I look on SQL there is ok example with error - insert into calendar (tema, pol_1, dodal, date_from, date_to, user_id, status) Values ('test' , 'test' , ...more >>

If I have two tables that I want to synchronize via replication publish subscriber model and they don't contain primary keys i.e. an ID field, but there are three columns that make a unique key. The tables are HUGE so I want to minimize down time. Can replication still be done, either snap...more >>

Which lock type or isolation level should I use to be sure that no one will read or write or do anything with the table I'm using? Code block should look something like this: lock table read value from table change value to new_value update table set value = new_value release...more >>

hi all and sorry for my english I write into a SqlServer2000 database via jdbc using a PreparedStatement (pstmt) and one of fields is a TimeStamp, my code is: qwdum=System.currentTimeMillis(); pstmt.setTimestamp(8 , new Timestamp(qwdum)); sometimes the field on table is correct (the millise...more >>

hi all, I am considering what should be the best way of implementing the following requirement. I've got a SQL2K production server. Now I've got another machine as the standby machine for this server so I'm thinking what method should I be using for this. Should I be using log shipping or R...more >>

Try hard to become familiar with T-SQL. Can anybodey tell me the best way to deal with set's provided by a stored procedure. Til yesterday I thougt trapping set in temp table using INSERT EXEC is a way out of this, but then I struggeled with nested INSERT EXEC's. What are all the system pro...more >>

I have a table using an identity column as its Primary Key and two columns (table reduced for simplicity) EmployeeNumber and ArrivalTime. CREATE TABLE [tblRecords] ( [ID] [bigint] IDENTITY (1, 1) NOT NULL , [EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [A...more >>

I want to copy all columns from tblA into tblB where tblA.UniqueID = parameter @ID without having to list each column name in an insert statement. Is this possible? Thanks, lq ...more >>

I get Invalid object name 'bstr'. when I try to run this query Select distinct c0.oid, c1.Value, c2.Value, c3.Value From (SELECT oid FROM dbo.COREAttribute WHERE CLSID IN ( '{1449DB2B-DB97-11D6-A551-00B0D021E10A}', '{1449DB2D-DB97-11D6-A551-00B0D021E10A}', '{1449DB2F-DB97-11D6-A551-00B0D021...more >>

I'm experiencing some very odd behavior in Enterprise Manager. I have a process that performs the following: 1.) Restores a published database from Server A onto Server B. The databases are identical and they are both published. 2.) I run a script that then deletes the subscriptions to the ...more >>

Hello. I'm working on an application where the original developer used the field name 'index'. This works ok when accessing the table from MS Access through query, but when I try to reference it via SQL with either a statement from Enterprise Manager or a stored procedure I'm getting an error...more >>

I've got a data set on which I'm trying to perform the same as excel's vlookup formula using SQL, but do not know how. Can anyone help? If you picture me having two tables Table_A and Table_B and they look like this; Table_A Name Sport John Football John Rugby Steve Cycling Steve Running ...more >>

SQL2K on W2Kserver I need some help revamping a rather complicated query. I've given the table and existing query information below. (FYI, changing the database structure is right out.) The current query lists addresses with two particular types ('MN30D843J2', 'SC93JDL39D'). I need to ch...more >>

Hi all, I have 5 databases, each about 20GB in size. I need to copy the data to a new server for reporting purposes. Initially I wanted to setup transactional replication; however, the database schemas often change (adding or modifying tables) and in order to accomodate those changes through re...more >>

Could someone please explain the pros/cons of installing the Veritas SQL agent versus backing up the .bak file generated by SQL Server? I have worked with both setups in the past, but never really understood what made one better than another. TIA, Brad ...more >>

Hi there, I've been sent a backup file from a SQL Server 2000 DB and tried restoring into a blank DB (with the same name) on my SQL Server 2K, only it errors, see below: (I've separated each line with a return, to make it more readable) 2005-07-08 11:08:23.93 spid51 BackupIoRequest::Wait...more >>

SQLServer 2000, after installing SP4 I get a lot of stop errors as noted in the subject. KB suggests hardware failure but so far I haven't found any faulty componants. The errors started happening just after installing SP4. Anyone else noticed this? ...more >>

I have a unique index based on the following columns: ProjectID (int) MaterialCatalogID (int) Material catalogues are pretty much static but projects are dynamic and people are most likely to be working on the latest project so would using a descending sort on the ProjectID in the index gain ...more >>

Hi, I'm currently using SQL Server 2000 SP3. I'm facing some problems which make me think of a SQL Server BUG. When executing a query, it seems that SQL Server tries to convert data even if not in the resultset -> This leads to SQL Server error. Here is a very easy sample to reproduce it : ...more >>

Hi, does anybody know a nice tool to automatically compare and show only the structural differences between two databases, this is, the differences in the columns of a table, constrains, data type, etc. Thanks J.A. ...more >>

Hi, I was hoping someone may be able to help me with a tricky T-SQL problem. I need to come up with a SELECT statement that does basically the following: Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from [StageDb].[dbo].tblPersonInfo group by RCRD_REFNO However, I need to select O...more >>