Can I create clustered index on columns having dupliacte data. If I can, how does it work Thanks in advance...more >>

Can I create Clustered Index on columns which has duplicate data. If I can, how does it work Thanks in advance...more >>

Hello Same type of question: How can I get the value of a field having the field Name in a variable ? Thanks for your help Thierry ...more >>

Hello I would like to get the content of a field based in the field Name. Suppose a table with a field Named 'LastName' for wich there is a trigger after update I store the field name in a local variable Set @ColName = 'LastName' How can I retrieve the value of the @ColName from the in...more >>

I am trying to us the xp_cmdshell SP for the first time and i am getting an error when i run it in Query analyzer i get an error this is what i am runnning xp_cmdshell "dir /b D:\IPS_PASS_OFF_FILE_DUMP\current\*.trg" This is the error Msg 50001, Level 1, State 50001 xpsql.cpp: Erro...more >>

Hello, I try to make it short, although my question may be difficult to explain. I am trying to migrate a DB to Oracle 9.i. There are error messages I dont understand and which are not in the FAQ; it has something to do with the user mapping from SQL Server to Oracle. I am able to migrate p...more >>

I need help for this problem: I have to copy a table from a Server A to another Server B the sintax I use is: SELECT * INTO TABLE FROM B.DB.DBO.TABLE the problem is that in the original table I have a primary key with an identity but in the new table the identity is not copied... p...more >>

procedure runs well on query analyzer, but it doesnt work on application. it returns error on asp. the ActiveConnection property of a Recordset object which has a Command object as its source. (3707) I suspect it's cursor problem. maybe i can't use default cursor, any suggestion? ...more >>

I'm seeing some strange performance problems with my SQL server (8.0 SP3). I have some large data conversion work to do and sometimes the server runs pretty quickly, and sometimes is simply appears to go idle in the middle of a long statement! Whenever I check the current activity on the proc...more >>

I have a project where I need to copy about 80 tables from a foreign database to my Sql Server 2000 system once a day. There is a fair amount of data to transfer and I want to make sure I don't lose any data. What I was going to do was do a "select into" command to copy into a backup datab...more >>

I have a query that counts the numbers of tickets by day of week. if NO tickets were issued on Friday the friday will not appear I still want a friday column to appear with the value of zero. i.e. this what i currently have as my output (no tickets issued on wen to sun). count of tic...more >>

I've seen many commercial DB's use binary(8) field as a primary key. Is there any performance advantages to utilizing this dt over an integer type? Also in performing selects, is the where clause to be more performant when specifying [binary] = [binary] vs [int] = [int]?...more >>

What are the standard rules regarding the Data Type of the result from a mathematical equation? e.g. 100.00/5; 100*5.5 etc. SELECT (Qty * Price) As Cost FROM .... (Qty is smallint and Price is decimal(10,4) Previously, I have got burned by not explicitly using the CAST (or convert)...more >>

Hi, Does anybody know if the IIF function also exists in Sybase 9? ...more >>

Table names are stored in sysobjects. Anyone have any idea where temp table names are stored? Thanks. ...more >>

Does anyone have an example of how to use a trigger to pull the data that was just inserted into a table?...more >>

I have been reading recently about the problems with using temp tables (#tables). I use them to page queries used for web applications. Is it possible to page data without using the temp tables? If so can someone point me to a sample. For the life of me I cannot figure out a way to page...more >>

How can I save image to a table in sql? If someone have a good example! Hrcko ...more >>

Hello fellow SQL users. I am developing an online store, and as part of the code each time an order changes status (ie, pending -> complete -> cancel) it updates the totals for each item in the order to reflect this, so that when I am looking at individual items it is easy to tell how many of ...more >>

Hi guys, I'm using FTS on sql server 2000 I would like to write a stored proc that accepts conditional CONTAINS. This means that if @param1 was provided I want to apply CONTAINS with certain column if not apply it to the other column WHERE CASE WHEN @param2 IS NULL THEN ...more >>

ho can I search through google archive of this newsgroup? ...more >>

Hello, We are using Access ADP as a front end, SQL Server 2000 as a back end. We have a customer contact database. We would like to limit certain users to only receive certain records based on Windows NT group membership. For example, Eastern Sales Group can see clients located in their re...more >>

I have a table which has no primary key, yet when I script it the=20 Create Unique Clustered Index (PK_Table_Name) ...... is present in the = script. I have looked at books online, and I can't seem to find an explanation = for why someone would put a unique clustered index on a table, but not ...more >>

hi i would like to know which approach is faster and why thnx SELECT GroupId, GroupName FROM [SecurityGroup] WHERE GroupId IN ( SELECT sgm.GroupId FROM dbo.SecurityGroupMembership sgm, [user] u WHERE sgm.LoginName =u.LoginName AND u.LoginName= 'user2' ) SELECT sgm.GroupId, GroupNam...more >>

I have a view that has several tables joined by 2 common fields. I need to exclude records from the result if they are not in one of the tables. Does the <> operator on the joins do this? TIA ...more >>

I have 2 tables that can be joined on a common column. I want to join the 2 tables and process the joined table via SQL Exampe Table 1 - field PK1 F1 J 1 10 10 2 10 11 3 20 12 Table 2 - field F2 J 5 10 ...more >>

Hi guys, again, does anyone know how to catch errors on profiler? what filter can you use? I don't want to catch good queries but only bad queries. ...more >>

How would you say is this date between these two dates with out time being factored in? like say the date your looking for is 4/23/2004 and you asking is it in the range of the days 4/23/2004 to 4/23/2004, well it should be because that is a date range of one day I thought it would work s...more >>

Given this query: select * from someTable where someField = coalesce(@someVariable, someField) This is the form I use to allow a field to be "optional" in a query. However, I recall someone making a post explaining that the problem with this form is that if someField is an indexed ...more >>

Hi, I am trying to write a stored procedure with parameters that will insert if the record does not exist and update the record if it does exist. Can someone help me with this? Thanks ...more >>

How would you go about doing something like using the returned table from another stored procedure in a select statement to see if something exists in it Say I have stored procedure SP_GetIDNumbers which returns a list of ID numbers and I want to do this SELECT Persons.* from Persons wh...more >>

I have several tables involved in my application, but the two in question here are the company and address tables. The company table has business_address_id and mailing_address_id columns, which are both foreign keys to the address table. So, the stored procedure to which my SelectCommand poi...more >>

I have a query like this SELECT Field1, Field2, etc FROM MyTable WHERE (Field5 LIKE 'blah%' OR Field5 IN (SELECT Fl FROM Table2) and people complained it is too slow. I made it faster by rewriting the code this way: SELECT Field1, Field2, etc FROM MyTable WHERE Fiel...more >>

Dear Group, Somewhere in the past I read that SQL Server 2000 now has a system table that will hold all constraint info that is traditionally put in check constraints in it. This was promoted as an alternative to Check Constraints. I cant find any references to this. If anyone reading this...more >>

I have the following results after a sql query: off_time mydata2 -------- -------- 12:15 4.324 12:30 5.26 12:45 6.9654 using this sql statement: select off_time, least(2+(0.044117*CT_OFF_GEN),8) as mydata2 from b_NRatio ...more >>

Hello all I have an issue with indexing an inventory table which is keyed by 2 fields heres the issue: the 2 fields are ProductionNumber and PartNumber 1000 NULL (could be '') 1000 A 1000 B 1000 ...more >>

What is the easiest way to modify the Table_Rowcount Stored Procedure, to strip off the KB characters output characters and change the format from character to integer? Thanks, Dan Output from Table_Rowcount Name rows reserved data index_Size unused CT_Acct 6958 91...more >>

I wrote a piece of code that has to go into a sproc using two different approaches: 1. A big SELECT statement with 5 LEFT OUTER JOINs and lots of subqueries 2. A temp table with one INSERT and several UPDATE statements, then a SELECT to return the data from the temp table #1 is fa...more >>

Hello everyone, Being that my question is not being answered on the .msde newsgroup, I figured I'd post this here. Someone out there has to know the answer to this... With my asp.net application, any time I try to connect to a sql server instance, I get: 'ResourcePool' is not supported o...more >>

I have two sets of documents, I lack english knowledge to call them with correct english terms, so I appologize for the inconvinience. The story goes like this: I have several storage areas accross the country. Some of them are wholesales, some of them are 'not'-wholesales. The difference is...more >>

I have a trigger where performance has degraded noticeabley. Can anyone help The code can be reduced as follow CREATE TRIGGER trPOLI ON tblPoli AFTER UPDAT A IF 1 = 0 -- FALS BEGI DoSomething1 based on "Inserted" tabl EN IF FALSEBEGI DoSomething2 based on "Inserted" tabl EN IF ...more >>

Given the classic master-detail relationship, as in the following example: InvoiceMaster: InvoiceNum, Date, InvoiceTotal InvoiceDetail: InvoiceNum, Item, Total it would SEEM to me that the total in the master record is a normalization problem in that the total is redundant to the individua...more >>

I have a scheduled job in sql server. This job is a executes a stored procedure. I want it's output in .txt file; i.e. how many rows updated/affected etc When I execute this procedure from Sql analyser, the output windows displays number of rows affected/updated. I want this same output in .txt f...more >>

How can I change the script listed below not to place a space between each output record. Looping through the cursor causes the space. I do not want to create temp table to store the samples. There's got to be easy way to resolve this problem. Thanks, Dan T-SQL (Cursor) ...more >>

Dear Friends I am facing problem due to the fact that my user have deleted the record and now the data is not available i want to trace who have deleted the records so that we can ask the purpose of doing the same to him / her Your earlier reply would be a great help. Best regards S...more >>

what is difference between index seek and index scan...more >>

Hello I have a table that I want to build a view on. I want the top 1 record for each record in the table. The a record can receive up to 4 rankings. I want to pick the top rank. The field, however is not numeric, but it is alphabetical. How do you get the top rank for each record Thank you Br...more >>

I execute this command from query analyzer restore headeronly from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MYDB_LOG while the server was doing the backup log on the same file Before this operatino the backup log was about 300 mb, after 11 MB. In the previous file there were ...more >>

I have to bcp the datetime in yyyy-mm-dd format. How to use the convert function to achieve this?...more >>

some of our application tables uses the char datatype for the tables. This is a problem because char does not support unicode, so each field for each record being compared must be converted. This affects the performance of queries. It is suggested that we change all "char" and "varchar...more >>

I want to select data with a date that is one week day less than toda e.g. 23/04/04 (Friday) would return 22/04/04 (Thursday 26/04/04 (Monday) would return 23/04/04 (Friday 24/04/04 (Saturday) would return 23/04/04 (Friday Thanks...more >>

i'm that newbie, i've been reading books on mSQL, i just want to hear it from a laymans term (simple explanations) ...more >>

Hi I am using the following statemen EXEC master..xp_sendmail @recipients = @sEmailAddress, @query = @sQuery @subject = 'TERM Survey Output' @message = @sMessageBody @attach_results = 'TRUE', @width = 108 @no_header = TRUE @ansi_attachment = TRUE Now everyt...more >>

Hello, We are running SQL 2000 sp3a on a Windows 2003 machine. I am trying to delete a file using xp_cmdshell, here is my code exec xp_cmdshell 'del "E:\SQL Server Backup Files\Production\MyFile.txt"' However I am gettig "Access is Denied". The file does exist, and the statement xp...more >>

Hello Experts, Doesn't this statement run in a stored procedure correct? : exec('USE MYDB') What else do I have to make it "play"? Thanks in advance....more >>