Hi All, i want to Write the query which will give me the distinct top 1 records. see i have my table and data in that table is in this way. ReqNo Name Other 1 chirag 1 xyz 1 ABC 2 ZZZ 2 YYY 2 QQQ 3 NNN i want the output as f...more >>

Hello fellows, Is it possible to make a hyperlink field like we can do in MS Access? If it is possible then how can I do it? Any suggestions please? Thankx in advance. Geri *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded fo...more >>

am new at this and I am stuck... I am trying to backup an entire database and restore it on the same SQL server with a different name. I have this in the Query Analyzer BACKUP DATABASE merchant TO DISK = 'c:\merchant.bak' RESTORE FILELISTONLY FROM DISK = 'c:\merchant.bak' RESTORE ...more >>

The situation is like this: I have a record in a table that I want to read and I want the last committed version of it. The problem is that another transaction is updating the record at the same time in a verry long transaction. While this transaction is running I can not read the record...more >>

Am I even close to getting this right? I'm trying to allow people to execute my PopUp stored-procedure... and have it call xp_cmdShell... but WITHOUT giving them direct access to run xp_cmdShell with cmds of their own. I only get: [Microsoft][ODBC SQL Server Driver][SQL Server] You can on...more >>

Below, I'm trying to set @enewsDate to today's date like '3/2/2005'. How can I convert getdate() to m/d/yyyy format? Declare @recipEmail varchar Declare @enewsDate datetime set @recipEmail = 'myEmail@aol.com' set @enewsDate = CONVERT (varchar(12), getdate(), 101) SET NOCOUNT ON UPD...more >>

Hi, Can anybody help me on what is going wrong, please? I tried to pass an XML document as text parameter in a stored procedure where openxml is being used to insert data into temp tables. If I pass any XML document over 120k of size it goes in a loop and does not return any error message. T...more >>

I am having performance issues Timeout, blocking , long running stored procedures etc. Application is written in C sharp and uses database sql2000 sp3. I have about 25 databases, view is used that joins tables from all db with left outer join and multiple conditions in where clause. Onlin...more >>

Hi: I built a table for my website writen in ASP.NET and ADO.NET. Now there are more than 10,000,000 records inside and the performance is bad, even I use simple "select ..." sql script. It will take a long time to get the result. The problem is caused by too many records inside or someth...more >>

hi i want to write stored procedure which returns the values. for e.g there is a table t1. in this table i've 4 fields. and i want to retrieve all these fields using stored procedure. can anyone tell me how to write stored procedure for selecting all the vaues in sql server 2000. thanx ...more >>

Is there an "sp_" already inckuded in SQL servere 2000 that will return some properties of the database incouding users, (rights maybe?)?...more >>

Using SQL Server 2000 T-SQL.... How do I convert the binary output of PwdEncrypt() into a human-readable text-string of hex values? SELECT PWDENCRYPT(LOWER('SwordFish')) (Query Analyzer seems to do it automatically.) ...more >>

Hi, How can i get the #Temp Table value after finished the SP ? Is it possible through Sql Server profiler to hold the #Temp Table value ? Thanks, ...more >>

Hi, I have a table called opportunity, included within this table are a opclosed (date) , a companyid (integer) and some other columns. There are many opportunity records per companyid . I am only interested in closed opportunities i.e select * from opportunity where opclosed is not null ...more >>

Hi, I have a table called opportunity, included within this table are a opclosed (date) , a companyid (integer) and some other columns. There are many opportunity records per companyid . I am only interested in closed opportunities i.e select * from opportunity where opclosed is not nul...more >>

How i can synchrnozie the 2 db's. Currently we are taking backup of online server to local server. our db size is 100 MB in size and also we got poor bandwidh. whenever we take backup, its creating 100 mb file size per day, Is there any way to take backup only the updated one in the master, i mea...more >>

How can I have SQL convert a UTC int to a standard datatime. For example, the UTC int (1104178047) converts to 3/2/05 at 10:30 my local time or so. C++ has a function to do this, any tsql equiv? ...more >>

I am trying to change my addiction to T-SQL and start to use Stored Procedures instead. In this stored procedure below this ASP.NET code I need to add something to check to see if the emailaddress already exists in the table and if so, somehow return a value to the ASP.NET app stating the e...more >>

Hello all I am currently using the version of SQL Server 7 that came with my VB 6 Professional disk set. I was looking on Amazon and I saw SQL Server 2000 Developers Edition for £39.50. Worth changing to from my current SQL Server package? Any big advances in the programme? Anyone used SQL Se...more >>

Hi, Had a quick look around BOL and can't seem to find whether one can create an index on a temp table? Can anyone shed any light on this, please? Cheers, Simon ...more >>

Hi, Had a quick look around BOL and can't seem to find whether one can create an index on a temp table? Can anyone shed any light on this, please? Cheers, Simon ...more >>

Hello All, I am using COLUMNS_UPDATED in my triggers to audit changes on my tables. I test everything with Query Analyzer and everything looks perfect. But when I test the triggers with the applications written by our developers (using Visual Basic or C#) looks like these applications a...more >>

I did a test, create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS) create table #b(char_set2 varchar(50)collate Latin1_General_BIN) insert into #a values ('collate') insert into #b values ('collate') go select * from #a where char_set1='Collate' go select * from #b wh...more >>

hi , am wondering how to save or retrieve an image in sql server 2000 table using vb application or access thx *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Suppose I need to loop through each updated row, obtain some data from that row and send a message based on that row and data, so that if 5 rows are updated I need to send 5 different messages to 5 different people based on what is updated. I keep reading to NEVER use cursors in a trigger, bu...more >>

Hi, I've create a DTS package with 2 global parameters, I need to call this DTS from a Store Procedure and pass the 2 parameters. How Can I do that in T-SQL ? If it's not possible, what I want to do is the following : Copy data (with a date filter) from one table to another (on another SQL ...more >>

can anyone tell me in what situation you should force a hash join or merge join option in a query? ...more >>

Using SS2000. I see queries created using things like where ziprequestid = cast(' + @@requestid + ' as int) or left([prop_mkey2],5) = BranchData.zipCode or where branch + left(tblFollowup.prop_mkey2, 5) in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from tblZipReass...more >>

hi team i like to know what is the different between .bak and .dat, how it will help us take backup the database. Regards S Kaliyan ...more >>

We are running SQL2000 SP3 on two servers. One is Server 2003 the other is Server 2000. Both servers are linked to one another. The 2003 server is running DTC. We are running several lightweight jobs that are occasionally, and more often than we like, having the following error: Could n...more >>

I have 2 questions, 1 - I created a trigger on server1 with the following logic: if any of the comment field (12 in all) changes updated another table on server2 with the same Id field. How do I do that. See trigger below. 1. I create a link server to another server. it give me the follow...more >>

What is the proper way of locking a row for read? Here's what I'm trying to do: 1. Transaction 1 finds a row it likes. It locks it from being read by Transaction 2 2. Transaction 2 looks for a row, but does not get blocked and does not find the row that is being processed by Transaction 1. I...more >>

Hi all, I have a fixed width text file that I need to import into SQL Server. The file is very wide and extremely long, so I dont want to go through and manually create the columns in DTS. I do however want to try and use BULK INSERT to copy the records into my table. I thought the only ...more >>

I'm working on some simple stored procedures and want to understand how SQL Server/T-SQL works. I understand that when I declare a variable it has a null value, yet for a numeric variable I get a zero if I use a PRINT command or an output parameter.... Is SQL Server doing an implicit conversio...more >>

I have a table of names which i have exported into another table to give a count i.e Original table Name ---- john john john john tom tom tom james james tom select count(name) as total,name from table Gives me Total Name --------------- 4 john 4 tom...more >>

I am not sure if I am in the right place but I did not find an answer in the Access newsgroup so I thought I could post my problem here. I have sp that I am using for a report in Access. The sp looks like this select os, computerMake from tblComputer where os like @os\ I am using the do....more >>

My specific problem: I have a DB full of daily stock prices including the open, close, high, and low. I want to A) select all stocks whose current day high is lower then the previous day's high for at least 5 consecutive days and B) count the number of days that stock has displayed this tr...more >>

Hi I need some advice on the best approach to solve the following problem:- One important note: The data in the table is self referencing (hierarchical) 1. There will be a server running SQL server 2000 enterprise edition and serveral lap tops that will be running MSDE 2. The laptops wil...more >>

Hi, I would like to find all the cases within all the stored procedures in a database where a particular word exists. Any suggestions? Thanks in advance. ...more >>

We have a constraint to check the validity of the username part of an email address. It does this by checking for valid characters. This worked fine up until we got our first user with a ' in their name - which I have discovered IS a valid character in an smtp address. Eg: fred.o'brien@dom...more >>

Does select top 0 * from tablename acquired shared lock? If it is then adding nolock to a select top 0 * from statement should always be the rule of thumb. example: ..... IF CHARINDEX(',address,', @TableList) > 0 exec sp_SelectByTable @ID ELSE SELECT top 0 * FROM address ...more >>

I setup a linked server in enterprise manager called remoteserver When I do a select on a distributed query using linked server it works fine.. select t1.id,t2.id from t1 join remoteserver.db.dbo.t2 on t1.id = t2.id The problem occurs when I do an update update t1 set t1.id=t2.id...more >>

Hi, I have a full backup of a Production database Call BidProposal last night, this morning at 11:00 a.m. something delete all data in the database in every table. I would like to clarify before I have any actions. 1. Should I make the Transaction Log Backup now? 2. I restore the full...more >>

Hi I am getting the above error with my SP. Why would I get this error? Many Thanks Jaco if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[qfm_RptgetAsbLevelGeog] GO ...more >>

Hello, Is there a way to get a list of fields in a table using Tsql? Thanks, Ron...more >>

I have started coding this script that is to be used when applying patches to the databaes behing our application. Besides applying the actual changes using osql, the script verifies that the changes have been added, permissions applied etc. But I have one problem I would like to ask about...more >>

Is there an API for creating SQL Server Diagrams programatically? Our datamarts structure(Additions/deletions of Tables/Columns/Relationships) can change at anytime. So I want my SQL Server diagrams to reflect all these changes automatically. There are many Datamarts, So I am looking forward t...more >>

Hi all, I am new to all this Sql stuff so go easy on me. I am having problems creating a SQL SELECT statement to select certain fields from two tables. This is a little hard to explain and if more information is needed please ask.. Using a SQL server(2k) with ADO 2.8 Relation...more >>

I have a series of database objects that represent things such as people, accounts, etc. I have a set of options (boolean) that I need to add to these objects. Normally I would just create a bit field for each one and be done with it. The challenge however is that there could be hundreds o...more >>

I have a query that returns the count of distinct values in a group by query, like so: SELECT FIELD_001, COUNT(DISTINCT FIELD_002) AS F002_Count FROM tbl GROUP BY FIELD_001 Instead of getting the full count of distinct values for FIELD_002, what I really want is a simple yes or no answer...more >>

I have a database in SQL Server in which i have data for every day of the year starting from last year. Now, I need to report the current month totals and last years same month total. Last year Feb 04 had 29 days vs this years 28 days hence, I need to write a code that brings the correct total...more >>

Dear All, I know there a way of checking the table properties in T- SQL but I can't find it on BOL, can someone point me to it ? J...more >>

Dear Sir, Yesterday during an Interview...i have faced some problem. I would like to clearify that NN (NOT NULL), ND (NOT DUPLICATE) AND NC(NOT CHANGE) constraints work ....when we define a Primary Key on any Table column. If, Yes( as i hope) then how and what's the meaning of this NC. ...more >>