I want to change some parameters in DB Maintenance Plan, for example, "remove log file if older than 2 weeks" to "remove log file if older than 1 days", programmatically, because there are many servers. I searched MSDB tables, it seems none of them contain such parameter info. How to cha...more >>

Could anyone please tell me from where I can read the SQL Server error log. Actually when I do database back then after some time I get some error wherein in the end is stated "..............See the SQL Server error log for more details. That's why I wanted to read what that log says. Thanks i...more >>

Hello there I've made by mistake a delete of data from table on the query Analyzer Is there a way to undo the last action or roll it back? ...more >>

Hello there I've made by mistake a delete of data from table Is there a way to roll back the last action or undo? ...more >>

What are the difference among SqlHelper.ExecuteReader, SqlHelper.ExecuteNonQuery and SqlHelper.ExecuteDataset?...more >>

Hi, I moved to SQL from Access. Got tables changed w/o a problem and figured out how to carry over forms and reports as well. However, cannot figure out howto carry over the queries. Should I copy and paste them as stored procedures? TIA Janet ...more >>

I have a table (MaxKey) with 2 columns to maintain unique numbers. "TableName" "MaxKeyValue" Everytime I need to get a unique number I execute this stored procedure from my program BUT I now suspect with many users on the system that they sometimes get the same maxkeyvalue, is that possible...more >>

I have a user table which has two columns with unique constraints (email and username). I have a stored procedure which inserts a new user into my table. When a constraint violation during the sp execution occurs, I'm trying to programmaticaly determine which of the constraints caused the violati...more >>

Hi Sql gurus, This could be a very newbie question : We implement many SProcs & Triggers in our application in Sql2k. During early implementation, many bug fix (on SProcs & Triggers) so that from month to month there could be upgrade version more and more. The problem is : When they BACKUP...more >>

Hi professor, How to put this string variable inside the EXEC statement: DECLARE @C varchar(10) SET @C='CC747' EXEC('DELETE FROM tblClient WHERE ClientID = ' + @C) It doesn't work. Best Regards, Henry Yu...more >>

Hi Friends, I have written query which will be like this Select PartNo, PartDesc fom PartMaster where PartNo in (101,102,103) It is giving the result like PartNo PartDesc ------- ----------- 101 Part1 102 Part2 103 P...more >>

This concerns the query optimizer in SQL Server 2000. In the Northwind database we wish to find all suppliers who have any seafood products in their product line.- Query 1 -------------------------------------------------- select s.CompanyName from Suppliers s where exists ( ...more >>

You need to make it an INSTEAD OF trigger, if you want to create it on a view. See SQL Server Books Online for more information. -- HTH, Vyas, MVP (SQL Server) http://vyaskn.tripod.com/ What hardware is your SQL Server running on? http://vyaskn.tripod.com/poll.htm "Aviv Scharf" <avivs...more >>

Hi, All: This SQL with UNICODE characters works OK to me and give me 2 results: SQL =3D "Select * from people where name=3DN'xxx'" But when I use stored procedure, it doesn't work, give me no results: SQL =3D "exec GetName N'xxx'" Create Procedure GetName @name as nvarchar(50) as s...more >>

Hello, I am working with an SQL Server 2000 database that was upsized from an an Access 2002 back-end using a recommended procedure for the whole process. It went completely without a hitch. When running a stored proc to insert data in a table I get an error message about a foreign key constr...more >>

I have a search form with many fields (about 6 int fields and 20 or so string fields). The idea here is the stored procedure will return all records if all fields are blank. As each field gets filled, the search will return only those records that match what is in the filled in fields. The WH...more >>

If I have a trace running, and SQL Server shuts down unexpectedly, there appears to be an oddity with the .Trc file Profiler creates. Profiler has no problem reading this file and displaying the data. However, the ::fn_trace_gettable() function is unable to read the trace file. I would think ...more >>

Hi All I am trying to find all candidates that have applied for jobs on a recruitment site, but group them by thier ethnic origins, showing the number of candidates from each ethnic origin. I have written the following query: select EthnicOrigins.Description, Count(EthnicOrigins.Description)...more >>

Can anyone offer any insight on an issue I am having. I have an Access database with three tables, each with 800,000 records in them. I am using a DTS Package to port them into SQL Server tables. The data load takes over an hour. This has become unacceptable. Any suggestions would be ...more >>

Hello, If I use the GROUP BY statement, I can get like three values - each in a record (= 3 records). Is there a way to make a select and get these three values in one record? I know I can use three selects inside a select, but is that the best way? Regards, /Mikael S ...more >>

Hello, How to detect "alter procedure X" or changes in syscomments.text ? Regrads, ...more >>

Does anyone know of an aggregate bitwise function such as a sum, but performs the bitwise or ( | )? Thanks in advance ...more >>

I am working on adding functionality to an application we have that is a SQL 2k back end with IIS 5.0 front end. I am adding the capability to transfer information using the MQSeries queueing software. To do this, I have written a DLL that is called from a DTS package which is in turn fired fro...more >>

I want to use sp_OAMethod to script out some tables in my db. However, I first want to script the tables w/o constraints, then I want to script the constraints only. The reason is when I try to run the script that includes constraints, I'm getting an error because some of the FK constraints re...more >>

Hi, how can i pass 2 ints to a division an return a float number with 2 decimals?? in the CASE 1 if Field1 = 81 and Field2 = 2 the returned Value is 40.00 instead of 40.50 CASE 2 works, but i need to try to do the query shorter because i got this in a huge dynamic query and i can't exceed the 4...more >>

Hello, I have the following stored procedure.... I would like to iterate through the resulting table's batchid values and use them in another query. How do I get at them? ------- CREATE PROCEDURE dbo.p_get_batch @LINEID tinyint = null, @STATIONID tinyint = null AS create table #batchn...more >>

Let's say we have a table named Table1 with an autoincremental column named Column1. Let's now say that last autogenerated value is reached 1000. I want to change this value to 1001 without inserting a new record. In other words I want to reserve the value 1001, and to be sure that it will not ...more >>

It there a free tool or utility available that will dump a MSSQL 2000 database to a SQL script WITH data? If so can you provide a link to it? Regards; Bob Dalton ...more >>

I hope this makes sense: In a Database containing object names A, B, or C and random positions ranging from 1-300 Million, I am trying to create a query that can find the occurances where all three objects are within proximity X from one another. This query has to be used on a GARGANORMOUS ...more >>

Hi i got the following problem, i'm creating a dinamic query inside one of my SProc, for later execute using sp_executesql, the problem is that i'm passing the limit of 4000 chars ( A BIG BIG Query with lot of calculations ) of the nvarchar type.... that is one of the parameters that i need to pa...more >>

Is there a way, when writing SQL to get a COUNT of the UNIQUE items in the list that is the result of a query?? An ex would be if I had a result set with an employee listed three times, and I was listed four times, can SQL give me a count of 2 instead of 7.......more >>

Hello, I was to be able to upload a file on to an FTP Site from SQL Server. I know that there is a DTS Task that can download files/file from and FTP site and this is not what I want to do. Instead, I want to be able to transfer a file on to an FTP Site (more or less Uploading not downloading). ...more >>

In an alter table/alter column statement is it possible to use one statement for multiple columns or do you have to use several statements each refrencing an individual column Bill *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded fo...more >>

Greetings, I need to search for occurences of an input parameter in 2 columns, and return the Part Number value in the other column. The returned Part Number must be unique, if there are dupliates return the one with the most recent updated date 1. Sample data, column ID is primary key ID ...more >>

I have to create a db structure for some "services" about some restaurants. Is there some links to learn some solutions ? Tipically is there this problem: There are some services that are 0/1 es: - It has japanese food (yes or no) - It has conditioned air (yes or no) but other ser...more >>

Is it possible to do something like this? declare @table1 as varchar(20) set @table1='TABLE_1' select * from @table1 Thih way gives no results. Is there any other way to do? ...more >>

Hi, i get this error in sql 7.0 Server: Msg 195, Level 15, State 10, Line 1 'GETUTCDATE' is not a recognized function name. any suggestion for replacement. i need to calculate the time in seconds from some date DECLARE @offset int SELECT @offset = DATEDIFF(second,G...more >>

Table Users FName(text), LName(text), UserID(number) Table Correspondence. Date(datetime), msg_From(number), msg_To(number) Table Users Data John, Quincy, 1 Jane, Doe, 2 Table Correspondence Data 2/2/2002, 1, 2 Ideally the query would return a Record that would look like. 2/2/2...more >>

Hi all SQL 2k dev ed I know I can see what triggers a certain table has by using: sp_helptrigger tablename but how do I get a list of ALL the triggers in my DB (order by triggername) ? Thanks for any help. Al...more >>

My query is SELECT TOP 1 Shipment_No FROM <MyTable> ORDER BY Control_no DESC I would like to get the results of this select into a variable for further use in a stored procedure SELECT @Shipment_No = TOP 1 Shipment_No FROM Tab_Aventis_Details ORDER BY Control_no DESC returns Incorrect synta...more >>

I have a table with 4 fields: id, name, type, and amount. I need to assign a name value to the parameter @name where the amount is the maximum. If there is more than one maximum amount, @name should be assigned the maximum amount of type 'X'. Any ideas? If not, how can I at least identify...more >>

Why would SELECT @@TEXTSIZE run from Query Analyzer return 64512 and the same return 2147483647 when run from Enterprise manager. Same server and database. TIA, Kiril...more >>

How can you programatically obtain a list of tables that are contained within a specific SQL Server database once a connection has been made to the database? I'm using ADO.NET with VB.NET and ASP.NET....more >>

I am performing a series of select..into statements against a linked server that is remotely hosted, along the lines of: SELECT * INTO dbo.mytable1 FROM [1.1.1.1].test.dbo.mytable1; SELECT * INTO dbo.mytable2 FROM [1.1.1.1].test.dbo.mytable2; SELECT * INTO dbo.mytable2 FROM [1.1.1.1].test.dbo....more >>

Does anyone know if Yukon will still support the old-style *= / =* joins in the where clause? Do you happen to have a ms url for this? Thanks, Wayne Sheffield ...more >>

Hi all, How can I get a list of PIDs of running processes that are connected to a database? I know the sp_who & sp_who2 returns a list of processes, but it's witout the OS PID. Thanks, Hagay. ...more >>

I am trying to query the same table in 20 different databases. My syntax is: select distinct a.fieldname, b.feildname from database1..table1 a,database1..table2 b where a.name = b.name and column1 = 'whatever' union all select distinct a.fieldname, b.feildname from database2..table1 a,da...more >>

Im trying to set up a columnvalidation using a list. In the list I have DKK and EUR but I would like for my users to be able to enter lowercase values like dkk and eur. But it looks like I have to extend my list to include the lowercase values? But this makes my dropdown look... well I would like...more >>

I have to have a 5 minute average of data that is collected every minute. Example: Timestamp, quantity, product 1:41,10,1 1:42,20,1 1:43,10,1 1:44,10,1 1:45,0,1 What I need as the resultset is 1:45, 10, 1 Any help is greatly appreciated. Thanks, Ken ...more >>

Does anyone know how to programmatically get the Data Definition Statement used to create a stored procedure not using the SQLD-DMO library? Please email jafisher_99@excite.com...more >>

I am converting one of my software products from FlashFiler to SQL Server. The actual product programming (using ADO for connection and data operations) is complete and the database and all tables have been designed using MSSQL 2000 Developer Edition. I need to deploy the MSSQL database I have...more >>

I want to copy a database from 1 server to another.. I am geting an error that de local system account can't be used to copy over the network any ideas how to do it ? ...more >>

I would be grateful for help with the following query. TableOne has two fields: 'EmployeeName' and 'EmployeeStartDate'. TableTwo has two fields: 'StartingWage' and 'StartingWageDateImplemented'. TableOne holds two records: 'Smith', '1 - Jan - 2000' 'Jones', '1 - Sep - 2002' TableTwo hold...more >>

I'm using an INSTEAD OF trigger so that I can send the contents of an ntext field to a COM object. This works fine. However, how can I then proceed with the update within the trigger? Table Schema: CREATE TABLE TestNText (Col1 int, Col2 ntext) Trigger: CREATE TRIGGER trgTestNText...more >>

I have a rich custom text box with possibilites for font changes (bold /Italics/size)In order to save this as is which type of field do I use in sql2000 ? thx Vincent ...more >>

Table test2 ------ eid name 100 aa 200 bb 300 cc test3 ----- eid pct_time pct_total 100 40 100 30 200 20 200 50 200 10 300 100 I need an UPDATE statement which updates column 'pct_total' with sum (pct_time) for speific eid, without using an i...more >>

i have 2 tables one with header information and one with data. the header is like id int identity primary key clustered not null dt_stamp datetime defatult getdate() not null desc varchar(20) null the data is like fk_id int references header (id) not null code char(10) references another...more >>

Hi, i got a big query similar to the following one, and i wanted to do something like the following but i got Invalid Column Name errror thrown for when trying to calculate the 3 Field.... i know i can do it using again the whole sum calculation but then my query turns really huge... Select ...more >>

I have a web application that seems to be recompiling a stored procedure numerous times. Is there a way to figure out what object it is? Here is the locking info: LCK_M_X TAB: 7:946102411 [[COMPILE]] ...more >>

Hi all - I have the following code which works fine. Takes a while to run but it works. I would like to combine everything into a single view but I am cant figure it out because I didnt write the original code. Any ideas on how to do this? Thanks, Paul Hastings - - - Begin SQL Cod...more >>

Hello All, Hoping for some help... Problem seems simple enough to me, just can't get it to work. I want to create an update statement that will update the active/inactive status of my customers based on whether they have been invoiced in the last 12 months. this is what I have: UPDA...more >>

I had a module created in Access 2000 that would run a query retrieving job names. It would take that job name and search through the specified directories and if found a match it would then delete the file. I was wondering if this could be done through a stored procedure using SQL code...more >>

What does mean error message 'connection is in use by another statement'? I have not found any information in SQL Books as well as MSDN. Thanks ...more >>

I'm looking more for a concept here than an actual specific example. Lets say I have 2 tables, doc_hdr and doc_line. CREATE TABLE [dbo].[doc_hdr] ( [doc_no] [int] NOT NULL , [info] [varchar] (255) COLLATE French_CI_AS NULL ) ON [PRIMARY] CREATE TABLE [dbo].[doc_line] ( [doc_no] ...more >>

Hi, i got a simple query that includes a division.... my query: Select Field1/Field2 as DivResult, Filed3, Filed4 from table1 how can i do to not have problems with division by 0 if field2 is 0 and return a 0 in that case??? Thanks. Alex. ...more >>

Hi, Does anyone know why, when I try to add a trigger on a view I get the following message: Server: Msg 208, Level 16, State 4, Procedure ChannelTrigger, Line 2 Invalid object name 'vwRecording'. I checked that the view exists and that the spelling is correct. I also tried to add...more >>

table a PARTNO SEQUENCE_NO AAA 0 AAA 0 BBB 0 BBB 0 BBB 0 CCC 0 CCC 0 CCC 0 CCC 0 .... make the result like as following. PARTNO SEQUENCE_NO AAA 1 AAA 2 BBB 1 BBB 2 BBB 3 CCC ...more >>

My code: select * from LinkedSever..XXX.AAA A inner remote join LinkedServer..XXX.BBB B on A.CAMPO = B.CAMPO I get this error "Server: Msg 7376, Level 16, State 2, Line 1 Could not enforce the remote join hint for this query." Any body help me TIA Carlos Eduardo...more >>

I have a view which consists of a UNION ALL of three separate queries. When I perform a simple inner join with this view, the performance is terrible--about 9 seconds. If I distribute the join clause to each of the three queries (redundantly), the response is instantaneous. Also, if I change the...more >>

Hi, I have a this problem, Ii'm converting datetime to float using " select convert(float,getdate())" or " select cast (getdate() as float)" And the float number that I get is of the date 2 days back. I understand that this is a known problem, is there any solution. I'm using SQL 2...more >>

I want to create some kind of constraint or rule whereby I can only insert a value into a table if 1) there is a matching entry in the primary key of another table (a foreign key AND 2) another column in the referenced table, a bit column, is TRUE/1. The foriegn key bit is easy but the insistenc...more >>

Hi, I have a dts that imports data from other database and then performs some computation. The dts consumes lots of CPU resources for the computation and other processes running on sql server are not responding. I have tried to adjust the process priority or step priority but none of th...more >>

Hi all SQL Gurus, In SQL Server 2000: Is there any method to catch the exact database error message (with place holder values filled in) in a stored procedure or function and return the same to front end application ?(C#) While writing the stored procedure for the errors occuring in th...more >>

I have a test project that I am trying to code to be as fast as possible. The project is a lottery speculation project that uses .Net web services to return an XML recordset. The goal is to accept any number of 5 possible balls, and return a recordset of all past draws and possible draws tha...more >>

Is there any way we can directly store the image in a row in a database using the data type ntext,image.2 GB is the maximum size as per MS, but how do we copy the image into the table . Not , where we store the pointers in a row and the image is stored in the OS folder. please check and clari...more >>