I am doing a relatively straightforward select top 1 against a few joined tables, select involves some text e.g. select top n 'Name: ' + name from table1 join table2... When I select the top 2, 3, 5, 100, etc, it comes back no problem, but select top 1 hangs indefinitely! (I end up cancellin...more >>

I've written some SPROCs that declare variables like below using DECIMAL type. For future compatibility reasons with existing databases, I need to switch from DECIMAL to DOUBLE type. What would be the syntax to use on below variables to make them DOUBLE type? declare @sDowntime decimal(10, 6...more >>

Hello, Does anyone know of MS roadmap regarding Yukon's support for real spatial data types (like Oracle's SDO_Geometry) and support for spatial functions? We need a monolithic database for both geospatial and regular,non-geo data. I'd hate to have to convert from SQL Server to Oracle just for ...more >>

SQL2000 Enterprise SP3 When running a fairly sizable query, I get the following error: Cannot sort a row of size 1047, which is greater than the allowable maximum of 615 I have not seen this before. I am aware of the 8k record size limit. Any info is apprecieated. ...more >>

(W2K server SQL2000) In stored procedure this works perfectly: SELECT * FROM OpenQuery( ADSI,'<LDAP://DC=camo,DC=comr>;(&(objectCategory=Person)(objectClass=user)(s AMAccountName=john.doe));name, adspath;subtree') What is the correct syntax to put a Input parametr in place of user name ...more >>

I'm simplifying the statement, it works as follows so far. Select TableA.FieldA , (select fieldB from TableB where TableB.FieldA = TableB.FieldB) as FIELDC from tableA where tableA.ID = 99999. This works, I get the values from FieldA I need as well as FieldC What I now need is to get a fie...more >>

I have a user defined function taking a UUID as a parameter and returning a table CREATE FUNCTION dbo.fnDeploymentGetAllInComing ( @DeploymentID uniqueidentifier ) RETURNS TABLE AS RETURN select * from Messages where DeploymentID = @DeploymentID and Messages.Direction = 0 -- and b...more >>

Hi All, I would like to know number of used pages in a database file (not in the whole database!). Such information is returned by DBCC SHRINKFILE command ( ... CurrentSize: The number of 8-KB pages the file currently occupies. ... UsedPages The number of 8-KB pages currently used by t...more >>

We have built an online contact manager. We are trying to figure out how to accomplish the following: Please keep in mind that this is web-based. When you type in the box to find a client is it possible that as you are typing it will narrow your search. I have seen this done using JS, but...more >>

Hi, I have a view that is a union of two table, same table, but one in the active database, the other in the history database. we are trying to improve the query speed on the view, so we modified the indexes on the history data set table. Just wondering that whether we need to modifie...more >>

I have a table with a foreign key constraint. If I UPDATE the table on just the foreign key with a wrong value, I get the constraint error from Query Analyzer, but if I UPDATE several columns including the foreign key at a time, the row updates without generating the error. This is very strange ...more >>

Hello, I'm filling up a table to feed a Metrics Manager system from Cognos. For one of these staging tables i'm using the below insert instructions. My problem is that, when looking at the table, after the insert, the records are not in the 'logical' order that the instructions have been perfo...more >>

How can I make a trigger ignore an error and continue on finishing the process that caused the trigger. ...more >>

SQL 2000 I have a table of "Newspaper Ads". 4 columns. ClientID, AdvertiserID, AdID, AdText. --ClientID, Advertiser ID, ADID is primary key-- I want to return the Max(AdID) for each CLientID, AdvertiserID. Easy enough. -----select Max(AdID), ClientID, AdvertiserID from ADS group by ...more >>

I know table name as 'authors' is there any function which can tell me whether au_xname is a column in the table. can i use this function safely. SELECT COLUMNPROPERTY( OBJECT_ID ('authors'),'au_xname','PRECISION') ...more >>

Folks - SS 2000 Standard SP3. Where can I find whether or not a trigger is enabled? I would like to iterate through all triggers for a given db and determine if they are enabled or disabled. Can someone point me to where this nugget of data is stored for each trigger? Thanks! Please re...more >>

OK, books online tells me to make a selection from the QUERY menu. I was thinking this meant in the QUERY analyzer. However the REPLACE option is not there. It also said to try the FIND option. There is a FIND option but it is GREYED out. I have little experience here and it was a while back. ...more >>

If you have no big plans and/or are up late tonite, you can chat/flame with the guys at RAC (www.rac4sql.net). We will begin sometime between 9:00-9:30 PM pst/ 12:00-12:30 AM est. We're using Yahoo chat so if you don't have it you can download it from yahoo.To participate send your yahoo handl...more >>

Hi, Sorry if this is something I should know but.... In an example database I have a stock table. Each item of stock has a reference and so on. However, each item of stock can be in one of two states - empty or loaded. If a item is empty it requires different information (and thus different...more >>

Hi i am currently working on the indexs in an ad system that records the clicks and displays for all the ads show on our website. The table for recording the ads looks like this: CREATE TABLE [dbo].[tbl_sitebannercounter] ( [siteBannerCounterID] [int] IDENTITY (1, 1) NOT NULL , [currentDa...more >>

env: SQL 2000 SP3. Three questions: 1. BOL indicates "SET DEADLOCK_PRIORITY LOW" specifies that the current session is the preferred deadlock victim. I was wondering if there is any way to do the reverse, i.e., specify the current session NOT be the preferred deadlock victim. Yes, I...more >>

I have the below query: SELECT dbo.cp_RM00101.USERDEF1, dbo.cp_RM00106.Email_Recipient, dbo.cp_RM00101.CUSTNMBR FROM dbo.cp_RM00106 INNER JOIN dbo.cp_RM00101 ON dbo.cp_RM00106.CUSTNMBR = dbo.cp_RM00101.CUSTNMBR LEFT OUTER JOIN dbo.vwNextDayBatchDDC ON dbo.cp_RM00101.CUSTNMBR = dbo.vw...more >>

Hi, I have a "LastName" field, defined as string. How to get it from DB as the firstleter uppercase? Select FirstLetterUCase(LastNeme) from tUser? Thanks, ...more >>

I am filling a date field using GetDate( ), but when I query by this field, I have a date a a string, for the life of me I cannot get it to find the record, can some please help ...more >>

Hi, I am using ADOX to migrate tables from Access to SQL Server. Migrating most column attributes such as type, size, precision etc. is straight forward. But I cannot figure out how to migrate the "Description" property of a column from Access to SQL Server It seems I can get the "Descrip...more >>

What is the best program to manage code snippets? The program should support unicode(Korean). Thanks. ...more >>

Hi, How do you insert the output from the Stored Proc into a temp table. -PK ...more >>

what kind of query can I use to determine if a column participates as a primary/foreign key in a table. Many thanks and seasons greetings. ...more >>

Hi, How do i list all the objects that have been given grant privileges for a given Role. -PK ...more >>

First question: Why does MS feel it is acceptable to charge a developer to sumbit a bug report? Secondly, in the event that MS employees are reading this, below is a script that demonstrates an error in MSS 8.0. A user defined data type that has a default bound to it fails to supply a d...more >>

I've in my db one table that contain one field that have inside him many information that are subduvide for a bar ( | ). So I need read this field and create for each ocurrency one new record in a other table, like this; Sample: I've this: ID EVENT VALUE 0000...more >>

I need to know if there is a way to find the the id of records that have been deleted from a table. For example, I have an ID column with values 1-60 and 65-100, but 61-64 have been deleted from the table. Is there some trick that I can use to count the number of records missing? Please, no ...more >>

Group, how can i Schedule a stored procerure to be run at a specified time? Regards /Peter ...more >>

I'm generating and emailing a .csv file using SQL mail. My problem is how to eliminate NULLs in the data and not populate the .csv file with " " data instead. My code is below.... select isnull(convert(nvarchar,dateTimeEnrolled, 101),'') as [Enrollment Date] from ... The problem I'm encou...more >>

thanx ...more >>

I am trying to use the @query in xp_sendMail and put a variable (@Store) that is filled in a cursor. Please see below. I get error by the + in @query line. I tried putting the query in the sp_QueryStoreReceipentMessage, right in the @query line and it gave me the same error. Thanks, OPEN @MyC...more >>

All, I am trying to run DTS packages to create a 1.6 million row table on a server in Arizona and transport that result set to my database in Oregon. I am getting TCP/IP errors that the network folks are investigating. In the meantime, I am somewhat stuck because I need that data. (It is ...more >>

Hello, If we insert two records with the same PK value, there'll be a conflict of PK constraint. In order to handle this, should I, 1. check it before inserting it If not exists(SELECT * FROM tab WHERE pkval = @pkval) INSERT ... Else .... or 2. catch it in the application ...more >>

Hello Sorry for the long-winded post I have been expirimenting with the profiler utility and I am curious about some of the entries that appear in the trace log. Essentially, I'm running a C++ application that uses an ADO command to iteratively execute (100 times) a stored procedure. While ...more >>

Hi, is it possible to convert an boolean 'True' to String 'ok' in one selection phrase. Example ti_b is a boolean table colum "select ti_b from table1 where iiD = 1" returns 1 (True) I need as return value string "ok". Thanks Norbert ...more >>

How Can I test Updated( ) using a variable as the column name? ...more >>

I want to have an exclusive lock at row level for a sql statement in a transaction,to accomplish this I used (rowlock xlock) lock hint on SELECT statement,surprisingly it is not working for some of the tables is there any restriction using this hint?? Here is an example to reproduce...more >>

The following trigger gets a successful syntax check but generates an error when run create trigger [emailsend] on root.ae_a_aemail_ for inser a if (select actiontaken from root.ae_a_aemail_a) = 'Y exec master.dbo.xp_sendmail [@recipients = contactmc], [@subject = 'Your customer request'] [@...more >>

I need to write a sp that meets the following requirements... 1. Select the TOP n records. 2. Mark the selected records to prevent other threads from selecting this group. 3. Prevent other threads form selecting this group while this action is occurring. 4. Return the selected records to the c...more >>

I need to run an asp page from sql server. I got it to work using a stored procedure which runs xp_cmdshell to open a vb-script that opens the asp page. BUT, it only worked a few times. Now, whenever I run the stored procedure (through query analyzer) nothing happens. The vb script still ...more >>

My name is Jorge and I am having a problem with a sp that I could call it "sinister". I developed a SP that reads information and parameters in several tables to construct a temporary table that I am gonna insert data to be kinda a log table. When I run that Sp via Query Analyser its res...more >>

I am multiplying 2 column. One is a money type and the other is decimal Now let's say I multiply 8.22 * 5. I get 41.1. How can I alter my SQL to get 41.10? Or even better $41.10? I read you can put a dollar sign in front of numbers. Thanks for your help...more >>

Hello, Someone knows the way to validate a username & password with sysusers ??? I am using SQL Server 7. I am doing something like is: SELECT * FROM sysusers WHERE name = @LOGIN AND PASSWORD = @PASSWORD; but i can not find in sysusers a field to 'password', so, what field i need ...more >>

this was kind of a hard question to search for an answer for!! but I don't think it should be difficult I have a stored procedure that passes a @emailMessage to a another sp to send an email. Below I go through a cursor concatenating together information to @emailMessage. I however would like ...more >>

Hi, How do I list all databases that have a certain table name in a field? Obviously some databases will not even contain a certain table name, and those that do, may not contain the certain filed. So what I want is create some databases, that contain a table "system" with a column "type"...more >>

I need to return the value calculated via an executable back to my stored procedure. Here is some code based off my actual code: declare @cmd varchar(100) declare @v_in varchar(6) declare @v_out varchar(64) set @v_in = '123456' set @cmd = 'c:/abc.exe ' + @v_in --abc.exe returns a 64 di...more >>

H I have a set of answer from a survey in a table. There are questions with multiple answers and I want to parse them out and count them. These questions are mostly from checked boxes and radio buttons. I need help writing a procedure to do so. Below I put in sample test data, what I would lik...more >>

Hi there, I found the next statement in an article: SELECT name FROM Northwind..sysobjects WHERE type = 'U' AND (objectproperty(object_id(name), 'tablehasidentity') = 1) When I run this it returns only the table dtproperties. However, when I look in Northwind I find more usertables...more >>

How can i explicitly accure row level exclusive lock, i found about row level table and page lock but is it possible to have row level exclusive lock if yes how...more >>

How can I store lets say 999 billion or a trillion (999,999,999,999,999.99) in SQL Server 2000 database, what data type to use for this. And secondly how can I save employee photos in my database in SQL Server 2000 as can be done easily in Microsoft Access. I need help....more >>

Hi: In my research about CONVERT(), I noticed the date format I'm interested in can be expressed using two different style arguments, 20 and 120, but the documentation doesn't really explain the difference between them. Sample queries: SELECT convert (char(19), d, 20) FROM t; SEL...more >>

Greeetings: After spending several hours going over the manual and newsgroup archives, I'm not much closer to figuring out the solution to my question. I have a feeling the exact solution I'm looking for doesn't exist, but I hope you can help by proving me wrong, please. Quick background, ...more >>

Is it possible to INSERT a tuple in a VIEW that contains data from more than one table? A message comes up and says: [View or function 'CustomerView' is not updatable because the modification affects multiple base tables.] ...more >>

Can anyone give a security role I couldn't define a security role by the following commands: addrole sp_addrolemember sp_addsrvrolemember Would you please give a full sample? ...more >>

Hi, I want to add some code to the default Stored Procedure template so that when developers createa a new SP would have some code already added. Anyone know where its stored and how to edit it? Thanks in advance. Regards, Sarmad...more >>