Hi all, If anyone can shed any light on this I would be very grateful.. Event Type: Error Event Source: Application Error Event Category: (100) Event ID: 1000 Date: 09/11/2004 Time: 20:03:03 User: N/A Computer: TITUS Description: Faulting application mmc.exe, version 5.2.3790.0, fa...more >>

Hi All, Is there a simple way of deleting records in one table and also those tables related in another table. Create Table Table1 (PID VarChar(50), RID VarChar(50)) Create Table Table2 (RID VarChar(50)) --------------------------- DELETE FROM Table1 WHERE PID = @pidVal...more >>

Please help me convert an access query. I have the following alias formula in an access query that I'd like to convert for SQL server for an Access Data Project: (To a Function?) AcumTime: Format(DSum("[SPTime]","qryPrograms"),"h\.nn\.ss") SPTime is time in hours minutes and seconds exam...more >>

The error message is: Could not find database ID 102. Database may not be activated yet or may be in transition. A view with the following as a part of a union query runs fine. SELECT * FROM vwIAPSRTWRK I INNER JOIN tblState W ON I.NextActionID = W.ActionID INNER JOIN Action A ON W....more >>

sql-server 2000 developer edition, sp3 windows xp pro sp1 anyone have insight into the following error message ? my sp is attempting to query a linked server. the linked-server has been successfully added in sem via ip address (can view tables, etc.) the linked-server is designated in ...more >>

Hi, In the following query, I cannot get records if the "IN" clause is not = satisfied. I would like to have the same clause in a way similar to an = outer join, so that the records from tables ce1, ce2, ce3, ce4 are = always returned. Any idea on how to do that? Thanks. Mike SELE...more >>

Does sql server operate differnetly with respect to performance if you use simple recovery over full? Or is the only difference between them what you can restore to if you have to restore? In simple what then is in the log for it to remain so small? Does the log grow in simple mode? ...more >>

I have a table with codes in it and I want to return the lookup values all in one query....an example is probably easiest: Code-Table: code desc ---- ---- 1 desc1 2 desc2 3 desc3 Data-Table: UserID Code1 Code2 ------ ----- ----- ABC 1 2 DEF 2 ...more >>

I'm writing a SP which does several update/insert within some related tables. And I need to serialize calls to this SP so if multiple users try to execute this SP at same time, everyone have to wait for a previous call end. To do it, I start the SP body with SET TRANS ISOLATION LEVEL SERIALIZABL...more >>

I am receiving different return codes from xp_cmdshell depending on the operating system. I execute the following code: declare @retcd int exec @retcd=master..xp_cmdshell 'del file.txt', no_output print @retcd (Note: The file file.txt does not exist) When SQL Server is running on... ...more >>

Hi I can do SELECT field1,field2 FROM Table1 GROUP BY field1,field2 no problem. field1 and field2 are integer fields >= 0. But in this case for each row value in field1, I only want (rowvalue,0) if zero exists on field2 for that rowvalue in field1 otherwise all non-zero entries for t...more >>

Is there any stored procedure which returns the paths of the physical data file and log file of a database? TIA KM ...more >>

Hi, I am trying to find out how to use the timestamp field. I understand that it is just a binary field containing an unique value and the field is updated every time the row is updated or inserted. Here is what I want to get done... 1) All my tables contain a timestamp field 2) I w...more >>

I just was given this task almost a month ago and am now asking for desperate help. We have a table as shown below and need to get the databack as shown below. Currently we have a programmer who is doing it by hand, but it is slow. Managment "feels" that there is a way to do this via SQL whic...more >>

Hi all, I have a database on my local machine set to backup each night - I used the gui / wizard in Enterprise Manager to configure that and to set the scheduling options. I have noticed that this thing just grows, and grows and grows....I very much like the way that when I opt to 'restore...more >>

Hi Is there any way that I can get a xml string back from SQL. I used FOR XML EXPLICIT but its not returning the fields that has no values (Null) How should I get them also Ex: This is what it should be <Employee> <Name>XYZ</Name><Age>28</Age></email> </Employ...more >>

Is there a way to use read data from an XML file, and insert it into a table? The code example below is from BOL using OPENXML, but doesn't call to a file directly. Thanks in advance. Mark DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc OUTPUT, N'<ROOT> <Customers C...more >>

If a stored procedure or a user function contains multiple insert/update statements, there're too many lines which contain almost identical codes like: IF @@ERROR <> 0 ... Is there a way to say server automatically RAISERROR if any error occurs within SP or UDF? ...more >>

Hi Freinds, Wha arethe best varibles for monitoringSQL server from win performance monitor. Just a regular monitor, to find out about server is healty and running fine. Thanks in advance, Pat ...more >>

I am writing a stored proc for a search that has parameters for each filtering criteria of the result set. I want to be able to search on any combination of the parameters. Each parameter is initially set to null so i only have to set the ones that I want to search on. I have seen the foll...more >>

Hello If I do : SELECT name FROM my_table WHERE id > 100 And it returns me --------- egan egan egan egan perry perry blagg blagg blagg egan egan egan ------ What is the SQL command that only makes the names appear once ? -------- egan perry blagg ----- Than...more >>

My company sells magazines. Some of the mags we sell are ones that we own (publish). Others are owned by an external vendor. Occassionally a particular magazine changes hands such as when our company acquires a magazine that was formerly owned by an external vendor. The reverse is also possibl...more >>

Hi, I do the Backup via EM. When I do a full backup of the database, does it also truncate the transaction log, too or I have to do the transactional backup in order to truncate the transaction log, but not shrink the log file. Thanks Ed ...more >>

I have a function returning a table. ========== CREATE FUNCTION fnExample() RETURNS @exTable TABLE ( f1 char(8) , f2 datetime , f3 integer ) AS BEGIN -- MAIN -- Fill @exTable... RETURN END ========== Rows returned from the @exTable is not UNIQUE. therefore I cannot use UNIQU...more >>

Hi all I need to be sure a table had not been updated since it was created. Is there a way to extract a log of updates, deletions and inserts commited against a table from sql server?? TIA Rea ...more >>

Hi, I am having two Tables Master and Child with the schemas Master ( Id INT, EditedById INT, SentState VARCHAR(50) ) Id is the Primary Key Child ( MessageId INT, Recipie...more >>

I need to 'fix" a poorly designed database. I have a field with comma delimited values in it. I have lost the political battle to re-design the tables correctly. I am resigned to a workaround. drop table [dbo].[tblTest1] GO CREATE TABLE [dbo].[tblTest1] ( [ContactID] [int] NULL , [TestD...more >>

Schema ContactStatus StatusID int ContactID int CompanyID int StatusChange datetime Sample data: StatusID ContactID CompanyID StatusChange 200 100 2 1/1/2004 201 100 2 2/1/2004 ...more >>

I am trying to used the vbscript functionality when creating a job. Since this is very new to me I am going step by step. The following is my code. dim objCDONTS Set objCDONTS = Server.CreateObject("CDONTS.NewMail") Set objCDONTS = Nothing Below is the error that I am getting. Error Source...more >>

Hi guys I will appreciate any help with the following: I have two tables: Users and Books, and the BookUsers table which allows the many-to-many relationship between books and users: CREATE TABLE xusers ( Id int IDENTITY (1, 1) NOT NULL , Name varchar(50) NOT NULL , CONSTRAINT [PK_xuse...more >>

I have a script that generates an error on the following DDL when ran at the end of a long import script. The error is "xp_sendmail: Procedure expects parameter @user, which was not supplied". If I add @set_user as a parameter it doesn't help. If I add @user it tells me it is an invalid param...more >>

Hi, I have a stored procedure which is being inconsistant but not sure why. Here is the SP. CREATE PROCEDURE dbo.[proc_test_ad_hoc] @field as varchar(25), @criteria as varchar(25) AS exec ('select ' + @field + ' from tbl_referral_name where ' + @field + ' = ' + @criteria) GO ...more >>

Hi, I want to be able to pass a variable from VB6 into a stored procedure but the variable is a field name. i.e SELECT @FIELD FROM TBL_REFERRAL WHERE @FIELD = @CRITERIA. How can I do this? Appreciate the help Thanks Damon ...more >>

Hi group, I hope this is the correct group for this post. I've got the following problem: I've got two tables table1 and table2 the 2 tables contain the same information, a code field and some information fields and an imagecolumn table1: Code (PK) ..... Imagecolu...more >>

I am trying to find a way to extract run-time error descriptions from SQL Server. After running a statement I check the @@ERROR variable and if it's not zero, I select the Error Description from sysmessages and get something like the following: Invalid column name ''%.*ls''. I am trying ...more >>

I want to open up a text file and reorganize the data in it. Can someone give me direction as to how I might go about doing this in tsql? I was wanting to do this with the log file created for scheduled tasks. ...more >>

I'm having trouble getting query results that I want. Suppose the table looks like this: mewr_id, rpt_published, country_id, lang_id --------------------------------- id1, 1/2/2004, ab, en id2, 1/5/2004, ab, en id3, 1/8/2004, bc, en id4, 1/9/2004, bc, en id5, <NULL>, bc, en id6, 1/12/20...more >>

There are two questions here. First is there a VIEW or other suitable alternative to referencing sysindexes directly as follows? Secondly should the nonclustered indexes include the ShowID column or can I exclude that since it's already an index? Typically there may be anywhere from 10,000...more >>

Hi Anyone, I have what seems to be a simple SELECT statement to write but I cannot figure out how to get it done. I have an Orders table which has a one-to-many relationship with an OrderItems table. In the OrderItems table there is a status field(tinyint) which indicates the status of th...more >>

I have a stored procedure that sends mail, and it works fine on two Win2k servers running SQL Server 2000. However, we're upgrading one server to Windows 2003 Server, and the procedure fails on that system. I've tested it on 2 Win2k servers, and 2 Win2003 servers. I have had no luck with ei...more >>

On Friday, I was performing a long but uncomplicated query (only 8 left joins, 7 small reference table, 1 large reference table) and life was wonderful. Now the same query is taking 5 or 6 minutes. I did an execution plan in query analyzer and one table now has a row count of 14 million. (T...more >>

Is there a way to decrypt an object in the database without resorting to VSS? I inherited a database which has several hundred encrypted objects and I need to search for an occurences of particular columns. What is the painless way to go around this? Thank you....more >>

Hi I have a text box on a homepage that I want to use to search three fields in the database so far I have: Select * from Contacts where firstname like '%' + @SearchPhrase + '%' or surname like '%' + @SearchPhrase + '%' or company like '%' + @SearchPhrase + '%' Which works find...more >>

Hi There, i need to insert in to table with the value in differnt table. suppose table A has vaule (12.23 or 1.00) as a nvarchar which need to insert to table B as datetime with value (12:23 or 1:00). if you have any idea please let me know. Thanks....more >>

Is there an easy way to identify how many stored procs are encrypted in a database? TIA....more >>

What is the best way to drop multiple users from a database? Can you use the SP Dropuser to drop multiple users? If so what is the syntax? Thank you, Brady Snow McKinney, Texas...more >>

I am needing some help on generation a script that will delete multiple users from a database. Or what is the easiest way to drop 30 or so users from a database? Thank you, Brady snow McKinney, Texas...more >>

An application is executing a call through ODBC. ODBC has decided that is needs to get the metadata for a table so it issues a call such as this: Set FMTONLY On Select col1, col2 from table1 Set FMTONLY Off This call always times out. When looking at the Profiler output, we see this: Ba...more >>

I am new to Triggers and i am looking for some advice on how to carry out the following actions. I have a SQL table (Table1) that contains details of boxes weighed in a production system. The uniqueid on each box is their Barcode iD. When a box is weighed, it is appended into a separate table...more >>

when i try to insert a row with null values into a table whose columns have not null bcp fails. but it aborts the whole batch. is there any way only the faulty row will not be inserted but all the other rows in the batch may be inserted. -- Vivek T S Member Technical Staff (Inucom)...more >>

This concerns FDIC data about deposits at bank and thrift branches (the children) and the 'holding companies' that own them (the parents). I can obtain the data in either of two ways (alternate parents): 1) with the holding company that currently owns the branch, or 2) where the...more >>

im trying to generate a query for a report showing counts for how meny clients are at which status on all of our services. for example if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table2] GO ...more >>

How do you upload images or binary files into SQL Server tables? Thanks in advance....more >>

ok need to do this I have a sp that deletes multiple entries using an author as an argument. exec sp_delete (select author from books) I have had a look at the array trickery but do not think this is suitable any idea?...more >>

I am creating an installation program to install a SQL Database on a users computer. I need to get a list of SQL servers available on the domain to ask the user on which srever they want the database created or to install a copy of MSDE localy if none are available. I am using Visual Studio 2...more >>