Hi, My application was slow on loading a particular form. I call some SPs in load event, then decided to use Profiler to track it. There was a considerable delay on this statement(which of course I don't issue that, but SQL Server itself does): SET FMTONLY ON SP_EXECUTE 2 SET FMTONLY OFF What ...more >>

Hi all, My client asked me whether I could make the SQL Server icon (in the taskbar) disappear. I showed him Windows XP feature to 'Always Hide' the icon, but obviously the icon still shows up if the user click on the right arrow on the taskbar. The client wants to hide the icon for good, yet...more >>

Is there any way to hide stored_proc. by S.Ramesh...more >>

newbie question: In a CASE statement, I would like the THEN clause to be the value of another column in the same table. Is this possible? I don't see any examples of that in BOL. What I am trying to do is something like: CASE col1 WHEN 1 THEN "okay" WHEN 2 THEN p.LastName END As re...more >>

Hello... By code is there any way to detect all the existing instances that are running ina PC? ...more >>

Hello, I would like to perform a freetext-like search without the fulltext search engine. Here's a table. CREATE TABLE Clients ( itemID int IDENTITY (1, 1) NOT NULL , ClientName nvarchar (16) NOT NULL , Address nvarchar (64) NOT NULL CONSTRAINT DF_Clients_Address DEFAULT (''), CONS...more >>

Hi all The database has varchar as the datatype for storing the Latitude, (in = decimal format, ie.e of 31.2556) I need to query the database for areas above 31.5611 degrees. Howver, I always get a "cannot convert varchar to numeric error I have tried converting data on the fly, and I cannot ...more >>

When calling a proc from Query Analyzer, the output of the Print statements in the proc are all obviously batched together and displayed at the end. Is there any way to print something "right now", or flush the print buffer? Thanks. David Walker...more >>

Hi all, Ok, this query returns some rows twice. Whats wrong with my query? The unique column is ERCPID. How can I return the record just one time. The records returned twice are only in the table once. I'm sure it's in my where clause. please help ********** SELECT dbo.tempercp.erc...more >>

I have the need to remove a constraint on a table since I'm trying to alter the datatype of one of the columns. I know I can drop the constraint given the name, but since the name is auto generated (something like DF__WHRPT_ITV__Expor__45F365D3) I need a way to find this constraint name so that ...more >>

I have a view that produces a set of records for a report. When this report runs I would like it to be a 10% random sample of the total number of records in the view. I've tried putting rand() in both the select and the where, but it doesn't behave how I intuitively think it would. The ...more >>

Is it possible to have a trigger that watches for inserts on one table in Database A, and then make updates / inserts on a table in Database B? If so, any examples would be appriciated. ...more >>

Hi, I would like to know how to use OleDB IAlterTable. I'm currently working with database SQL Server 2000 CE and Access 2000 and I need to be able to rename a table. I just don't know where to start. How do I get access to this interface? What are the step to get it? Thanks ...more >>

can anyone provide an example of how to use the Enumtables methods in SQL DMO from VB.Net ? (I want to be able to get back the tables and schema from a linked server) tia ...more >>

I am trying to implement a database View that uses a division calculation. The business data for this application is such that it is possible (even likely) that a divide by zero condition can be encountered. These are the client's requirements -- I cannot change them! For example:...more >>

What is the best way to querie addresses? We want the user to be able to select this address out of the database: "100 N West Ln" But it could have been entered as: "100 N West Ln" "100 N West Ln." "100 N West Lane" "100 N. West Ln" "100 N. West Ln." "100 N. West Lane" "100 North Wes...more >>

Hi Everybody, I am using the SQL server 2000 and i want to fetch set of rows between suppose record number 10 to record number 20. This will fetch the records from 10 to 20. If there is any way to perform this task. does SQL support any inbuilt functionality for the same like Oracle suppor...more >>

can one of you gurus explain this one to me? i killed the following update after it failed to complete in 8 minutes: update a set a.number = b.number/c.number from a join b on a.field1 = b.field1 and a.field2 = b.field2 join c on a.field1 = c.field1 an...more >>

Hello...i'm having a problem with an SP (this SP contains an UPDATE), this SP is inserted in a app that i'm writting in VB.NET, and everytime i run the piece of code an error appears (SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server...more >>

I have a query that needs to call a User Defined Function but I can't get it to work. select l.lender_id, (select count(*) as total from dbo.getServiceLevels('07/30/04','08/01/04',l.lender_id)) as test from T_LENDERS as l When I try to run, it gives me the following error: Server: Msg ...more >>

I have noticed much of our code's "where clause"s look like this: Where TableA.Field1=TableB.Field1 and XYZField Like '%' and ABCField Like '%' Does SQLServer (we are running the 2000 version) know enough to run the query without trying to match these like expressions? Should...more >>

How can I determine by looking at the registry which edition of SQL Server 2000 is installed?...more >>

I have a client/server checkwriter (VB/SQL Server 2000) that seem to be running slowly upon commiting of the data back to the database. Originally it was written without UDFs (in SQL 7). But along came SQL 2000 and I decided to go with numerous UDFs to replace small procs that return a single ...more >>

What is the most efficient way to put an identical trigger on 100 tables? The tables and triggers would have different names, all else would be identical. TIA -- Mark Simmerman Napa, California, USA SQL Learner ...more >>

Hi all, My apologies if I am asking a question that has popped off the bottom of the post list. But some help would be greatly appreciated. We had a SQL 2000 Enterprise Edition SP3 (not 3a) Cluster, running on Windows 2000 SP3. The For XML Explicit Stored procs have never given a problem w...more >>

I have a stored procedure that loads an xml document and does some = calculations. This stored procedure is used heavily. I may not be able = to do this in the code as it will result in numerous calls to the = database. Is this call too expensive or resource intensive on the = server?=20 Than...more >>

Hi, I have 50 tables with data on primary filegroups that need to be move to the secondary filegroups How do I move the existing tables to the secondary filegroups? Can I write some type of ALTER TABLE? Thanks again, ...more >>

sdb..sp_update_job @job_name = 'test',@enabled =0 I want to issue the above statement using VB code. I'm having problems using the params method. I know you have to append each param into a collection. I cannot get the syntax correct Dim Param As New ADODB.Parameter Param.Name = "@job_nam...more >>

Hi freinds, I am trying to : kill 294 and getting this message : SPID 294: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds. but soid 294 is keep running!! How can I kill if permenently? I need to detatach the db and can't do it...more >>

I need some suggestions in finding out the cause for stored procedures time outs. My proc times out and then all of a sudden it starts working without any timing out problems. What can be done to further narrow it down? Thanks in advance....more >>

Hi, How much space would it take to index all the tables in a database? The database is about 1.2 TeraByte. How do you find out disk space usage for the index? Thanks in advance. ...more >>

I have a query that counts each of several columns. If these columns are empty, it does not count it. However, what I need to do is ADD two of these counts together in one statement? Any suggestions? Dennis ...more >>

can someone explain to me what this select is doing? declare @num as varchar(20) set @num = '0123-45x6' select PATINDEX('%[^0-9^-]%', @num) tia ...more >>

Hi ! I wish to use dynamic sql to execute "SET IDENTITY_INSERT" , like this: declare @ide nvarchar(50) set @ide = 'SET INDENTITY_INSERT TABLE1 ON' exec sp_executesql @ide ....and it doesn't return an error, however it doesn't carry out the "set...on" statement. Why? Thanks in advance...more >>

Hi, again, I am trying to use a stored procedure to select certain records and assign them a unique number, or id, that can start from a number that I choose ie, SELECT [Table], [Quantity Break 1] FROM dbo.[44PagerAug] WHERE [Quantity Break 1] IS NOT NULL I would then want to update the ...more >>

Hi, Is there an easy way to query for the tables (and views) that a view is built upon? Eg: view_1: SELECT * from view_2 INNER JOIN view_3... I would like to ask for all of view_1's underlying views and get view_2 and view_3. Thanks, -Paul ...more >>

Why can't a variable (an expression) be used as a table name? This T- SQL gives an error: Declare @Tablename varchar(20) Set @Tablename = 'Positions' Select top 10 * from @Tablename I get the message: Server: Msg 137, Level 15, State 2, Line 3 Must declare the variable '@Tablename'. ...more >>

Hello I have recently deployed a VB application that uses SQLDMO.dll. I have read in a reply to a previous post that this was redistributable, so I put the SQLDMO.dll in the package and deployment wizard package (it is only for the internal use of our company). The install package that the P...more >>

How can you perform a case sensitive search in a case-insensitive SQL installation? We have SQL 2k SP3 installed with Case insensitive default behavior. However, we do want to search against some data considering case sensitive match. Is there anyway you can do that in this kind of set up?...more >>

Hi I beleive I have a fair idea of how locking works, and I wish to apply some locking techniques to my stored procedures. My main stored procedure generate some dynamic sql, no isolation is needed for this procedure. After the outer (main) stored procedure created the dynamic sql I wish...more >>

Okay, here's a question. Before you blast me, remember "NOT A PROGRAMMER". My code below runs in SQL. I understand that I can basically set paging by using VB or ADO. My question is, how do I make the code I have below (pretty basic SQL), into VB/ADO with a statement for setting paging size? ...more >>

Is there a way to determine if any views are based on a linked server? -- Thanks Phil...more >>

I have a select that returns data and I would like to sequentially number the items begin returned before inserting into a table. There is no unique field within the data returned, I just want to number them as they are returned from the select ...more >>

HI, I have requirement where I need to do full text search on all the search text. For example, for the search text "Black Helmet" , I should get the following records having "Black Helmet" as well as "Helmet of Black". Please help on how it can be done. Regards, Vani...more >>

Hello. I have the following WHERE clause in a stored procedure... WHERE (FlitchNum = @FlitchNum) AND (BundleID in (@BundleIDs)) When I execute this code I use syntax such as... (BundleID in (1,5)) which essentially effects records where BundleID=1 or 5. How can I change this code to accept ...more >>

Hi all, there is a way to change the property "Language" of a Sql Server Registration ? I am using the same application to access 2 differents Servers and I have some problems formatting datetime values, because the first need gg/mm/aaaa and the second mm/gg/aaaa. Any suggestions for me ?...more >>

I am trying to return a date 20 working days back from today. I currently use a calendar file of dates and a flag to separate weekdays from weekends but I cannot work out a simple way to do this. ...more >>

-- TEST SP CODE create proc TestRowCount(@check int) as if (@check = 1) update mytable set myflag = 'Y' else update mytable set myflag = 'N' if (@@rowcount = 0) select 0 -- bad else select 1 -- good -- TEST CODE create table mytable(myflag char(1)) insert into mytable(myfla...more >>

Hello! SQL Server 2000, SP3a Access XP, all SPs Access adp I have a problem with a varchar field. (size = 8000) In a vba-routine I declare a string variable, and set a text to this variable; the text is not longer than some hundred characters. dim num as string num = “blablablabl...more >>

Hi, Will prefixing the table with the owner cause any performance improvements. Is it a suggested practice. thks in advance....more >>