Hi, I have a menu, with a list of months. I want to include an option "Any" which, if selected, will display results from all months ("any" month). It it were a text field, my "Any" option in the menu would have a value of "%" - what do I need as the value when working with an INT column? ...more >>

Hi I need to display each and every month and total customers shopped in a , say a supermarket by month and the new members shopped in very month in other column i.e the members shopped in a month which is not in the previous mont The Tabe is something like this Member transaction_date ...more >>

Hello What are adv / disadv of using Dynamic SQL against Static SQL in stored procedures Regards PVV30...more >>

Hi all, I have a nvarchar(25) field (user_name) which I want to create as index. This is proarbly the most used column on my entire application, as it uses to check login and session details on every webpage. What is the best index to create? Should I go with a user_name_checksum and crea...more >>

Hey Is there any way in sql server 2000 to add a comment/description to tables/columns? like in oracle 'add comment' Thanks in advance Martin...more >>

How to debug complicated stored procedure in sql server query analyzer or Profiler. I can set break point,stop there, can see the value of variable. thanks ...more >>

Hi, I am using a full-text catalog to perform search inside a 1GB table. I have created fulltext catalog as usual methods, selected the necessary fields for the catalog from the table. When I want to make a stress test over the search fucntion, it deoesnt return any data if I use more then...more >>

Hi I have 4 fields like this CREATE TABLE [Subs_Addresses] ( [Name] AS (coalesce([StreetName],'') + ' ' + coalesce([City],'') + ' ' + coalesce([State],'')) , [StreetName] [varchar] (255) NULL , [City] [varchar] (50) NULL , [State] [varchar] (40) NULL .... } As you can see, Nam...more >>

I could use datepart for each piece and concatenate, but is there a more elegant way to format a datetime field to MMDDYYYYHHMMSS? Jeff ...more >>

Can I separate a table and it's primary key to separate filegroups I have a table that I'd like assigned to filegroup "X" I have a primary key that I'd like assigned to filegroup "Y" I create the table and all is well. When I create the primary key, the table gets dragged to the pk's fi...more >>

Looking for a good tool to build views, stored procedures etc... (similiar to SQL Server Query Analyzer) that works with SQL Server and Oracle. Any suggestions would be appreciated, Mark ...more >>

Hi all, I have used the below CURSOR to Update the "PAYMENTCOUNT" field of the ACMAST table. This is working fine. But has a BIG PERFORMANCE Prob. As My Table has 5,00,000 Records. So Please Suggest How can I write a Single Update Statement using JOIN to DO the SAME JOB. set nocount on ...more >>

sql2k sp3 I want to update a table simply by adding '00' to the exisiting values in that table. Heres my crack at it: update AccessStores set StoreId = (select '00' + StoreId from AccessStores where StoreId like '[0-9][0-9][0-9]') but I get the Subquery returned more than 1 value...more >>

Can anyone tell me why cascade delete doesn't work when I delete a customer from the Customers table? What is happening now is that when a customer is deleted the records in CustomerCreditCards, CreditCards and ShippingAddresses are deleted, but the any of the records in the Addresses table....more >>

Hello all Please help , I have the following: CREATE TABLE [AbraDataDictionary] [ID] int COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Description] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL G INSERT INTO AbraDataDictionary VALUES ('A','Testing IF @@ERROR < print 'Err...more >>

Is it possible to get this code working? DECLARE @sTEMP AS VARCHAR(200) SET @sTEMP = '123,123,123,212' SELECT TOP 10 * FROM INVENTORY WHERE GROUP_ID IN (@sTEMP) @sTemp is a variable containing the group_id, normally its only one, but sometimes there is more then one, Can you somehow use var...more >>

Hi guys, I have a multilanguage table (tblLabel) with a suitable labelText for every labelLang (1= English, 2= French, 3= Spanish). Similiar texts (different language) are grouped with the same labelGroup. For example- [labelGroup] [labelLang] [labelText] 1 1 ...more >>

Hi, I want to store all the parameters of a sp. How can i store them in a table(so that i call call the sp with same values). Allmost all my sp's take 40-60 parameters. These sp are called during replication process. When any error occurs in a sp i want to store parameter of the sp in a so...more >>

Hi All, Please forgive the clumsiness of the following. I have an ASP page which will be used as a "filter-handler" to sort and filter through necessary network port info. On that page I have 3 "FilterBy" text boxes (1, 2, and 3) and 3 "OrderBy" text boxes (1, 2, and 3), along with 3 "SortBy" tex...more >>

Hello all I'm looking for a way to use the resultset from a stored procedure as the 'table' in the FROM clause of a select query For example, lets say I have a stored procedure called some_sproc which takes a single parameter in What I'd like to do is this select * from some_sproc(val1) I...more >>

What does it mean when "logical scan fragmentation" is missing from the results of DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES? ...more >>

hi i have the proc belo create procedure testt @productNumber varchar(10 a declare @varIssue varchar(10 declare @retCoverDate varchar(10 declare @returnVal varchar(10) DECLARE @sql NVARCHAR( 2000 ) @SQLx NVARCHAR( 2000 -----------loop begin ----------here it is getting ...more >>

I want to run a query in a SP along the lines of: Select * from [table] where field in ('val1', 'val2', 'val3') How do I pass the list of parameters through? We have an small but unknown number of these. I tried passing through a single string but it doesnt work - fails converting the str...more >>

Hi - I have a sql as follows: update table1 set field2= convert(varbinary(32),'NDYRIRUSIDJE=') where field1 = 'test123' Field2 is defined as a varbinary on the table. This works fine in SQL Query Analyzer. I then set this up as a stored procedure as follows and it does not convert. ...more >>

I am importing a table of about 940 columns and I always get an error <?xml version="1.0"?><Error><Record><HResult>0x80004005</HResult><SQLState>01000</SQLState><NativeError></NativeError><ErrorState>1</ErrorState><Severity>0</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Desc...more >>

Hi, Could anyone refer me to resource that compaires advantages and disadvantages of dynamic sql? Long time ago I saw some white paper called "the curse and blessing of dynamic sql", but I can not find it now !!! Any help would be apprecited, Allan ...more >>

Hello, I created a job that uses a linkserver to Oracle db for the past year. It ran fine until a few days ago when Oracle db schema changed. I modified my SQL schema to be the same as theirs but I am still getting an error. I ran the following on Query Analyzer. SELECT * FROM OracleSe...more >>

I have parameter @count in my SP. Now I would like to get top @count random records from my table. Something like this: CREATE PROCDURE test @count int AS SELECT top @count FROM myTable ORDER BY newId() How can I do that? If I write: SELECT top 5 FROM myTable ORDER BY newId() ...more >>

I'm reviewing a db design and the tables have quite a few columns that are nullable. What are some design varitions that could eliminate tables with many nullable columns? Links to more reading are acceptable. I know some of it could be eliminated by normalizing further. Which I would guess i...more >>

I am having a problem with the following query. It should return values from two different units (Banderas and Genesis) but it only returns the Genesis matches. I cannot see why? Any pointers appreciated. Wayne ================== Query ===================== SELECT t1.UnitName, t1.Unit...more >>

The "order by" clause does not recognize table prefixes in some cases. The result order is different even though the same "order by" column is used. Ordering is using the "select list" column in the first query, and the result is in the stated "order by" order in the second query. Is this by d...more >>

hi i am having problem with this trigge USE PO IF EXISTS (SELECT name FROM sysobject WHERE name = 'updlogpos' AND type = 'TR' DROP TRIGGER updlogpo G create Trigger updlogpo O PO_Data_Fil FOR Updat a begi insert into log_pos (id,col1,col2 SELECT 'OLD' id,col1,col2 FROM P...more >>

Hey all, An easy one, I am running SQL 2k on Win 2k Server. How do I search for a null condition in the WHERE clause. An example: SELECT tblWhatever.AudType, tblWhatever.AudDate FROM tblWhatever WHERE (ISNULL(tblWhatever.AudDate, NULL) LIKE 1) This doesn't work. How can I filter...more >>

Hi, I have a bunch of tables in database. I need to find out which tables contain values "wsbook", is it possible or no? Note: it's not table names, "wsbook" is data from tables ...more >>

Hi, I want to run an SQL statement that I have generated and stored in a VARCHAR variable. I want to be able to run the statement and store the output of that statement in a temp table. I've tried different variations of code (ie. using EXEC) to accomplish this, but nothing seems to b...more >>

I have a table with about 1.2 mil records. When i issue a delete statement like that: delete from table It runs forever. Clustered Index scan is performed by Sql Server! How to avoid index scanning by Sql Server? Table Definition: OID(int), Lname(varchar(50)), Fname(varchar(25), Zip(...more >>

I posted this message last week and got some responses and I thank for the resposes again over here. Unfortunately, I have not received an answer to the question I am concerning. Following are the previous messages. I am hoping someone, especially who is from Microsoft, could give me an answer. ...more >>

Hey folks! I've created a query within a trigger that returns a number of records. I need to insert these records into another table. However, I haven't been able to figure out how to retrieve anything other than the top record. I know you can set a while loop up in a stored procedure, but how...more >>

Trigger newbie I need to auto increment a Column that is an int datatype and is not the PK I am currently dealing with this in the front end but have worries about concurrancy issues and would like to handle this in the backend. Is this possible with a trigger using an aggregate statement...more >>

Hello following puzzle i got help solving long time ago but i cant find it anywhere, i guess newsgroupservers doesnt store every message..... anyway...... if wayne snyder (i think it was) or some guru could help me solve this one again i would appreciate it very much. if i can remember it c...more >>

I've been fooling with this for a couple of days now and can't figure it out. The first column in my query is a 10 digit order number. Excel is considering this column as numeric and converting it to scientific notation. I've tried adding double quotes to before and after the values and single ...more >>

Hi, all: How to read/write binary data from/to SQL server? Could please give me an example? Thanks in advaince kevin ...more >>

I would like to convert a string representing an hex number to an integer.Unfortunately the CONVERT function doesn't like CHAR or VARCHAR. SELECT CONVERT(INT, 0x1AB2) works very well but SELECT CONVERT(INT, "0x1AB2") gives an error message. btw: In my real problem, "0x1AB2" comes from a SUBS...more >>

Is there a way to incorporate DTS package execution using vbDotNet ? If so point me to some examples. Thanks Jim ...more >>

Can you - Define - Set - Access a connection specific variable that exists for the duration of a session?...more >>

We have a stored procedure that generates a rather complex report on a daily basis. The query is very resource intensive and causes other OLTP processes to time out due to extended delays. We have tried to optimize the query as much as possible, but it still seems to cause problems. The same ...more >>

Is this allowed? Also, is returning Table from a User-Defined Function a resource-saving (efficient) way of writing code? Or is there a better alternative thanks -Lawrence...more >>

Hi, Can you use the EXEC function in a UDF? TIA, Eric...more >>

Greetings, We load accounting data contained in delimited text files via a web = interface to a SQL 2000 DB. Each record in the file contains a person = id, some accounting information and an amount. Files typically contain = about 20,000 records. Each file is assigned a unique batch ID when i...more >>

Does anybody know an easy way to split a field based on delimeter in SQL? I.e. I need to split field1='{1,2}{2,3,4}{2,6}' into 3 fields: 1,2 2,3,4 2,6 May be by ising PATINDEX('%{_}%', field1)? Do I have to create cursor for it or there is an easier way? Any ideas would be appreciated. **...more >>

Can anyone explain this ? I am trying to gather data, including version number, for a number of remote servers, running MSSQL2000, by executing the sp xp_msver, and loading the result into a temporary table. The code looks like this: << Set @ExecString = rtrim(rtrim(@ServerName) + ...more >>

Hello All, Why would these two statements return different results? select isnumeric('0403267d2') select isnumeric('0403267c2') The first one returns 1 which from my understanding is incorrect. The second returns a 0. Any comments are appreciated. R...more >>

Hi, I was wondering is there a way i can query a database to find out what tables there are in the database which are user created, then using something like vb loop through to find out details of each table in turn, i.e. Table size, rows that kind of thing. Ive been searching all over the ne...more >>

How do I extract the AUFNR values from table #ImportWO that are not in table #WO (Correct result would be: 001, 005, 006 CREATE TABLE #ImportWO AUFNR nvarchar(12) NOT NULL B int -- not relevan CREATE TABLE #WO A1_WO_NO nvarchar(12) NOT NULL B int -- not relevan INSERT INTO #Imp...more >>

I've encountered a seemingly strange problem. I have a table with a column of type uniqueidentifier. I can query the table using the field in the WHERE clause, comparing to a string. For example: SELECT * FROM TableA WHERE UID = 'adadsfadsfadsfadsf' However, if I attempt to join a table w...more >>

I have created a view which picks up data from another view, i would like to have a structure in the view like if i can use if then else or atleast if then statements i need to get the dates compared with getdate() function ...more >>

I have two tables Table One: Has Customer Number, Customer Name, Sales Order Number , Address Code, Address Details, City, Postcode and a distinct Shipment Number This table only has details which are fixed when a customer is setup initially Table Two Has Customer Number, Customer Name, Sales O...more >>

I'm having timeout issues updating a Ntext field that is >8000 chars. In SQL Profiler, I am geting a Lock:Timeout when updating a row with > 8000 char Ntext field. I can update any row with a text field of less < 8000 char. Anyone know why SQL server will be timing out? Is there someth...more >>

I have a function that converts a varchar to a bigint using a set of rules. when I try to use that new bigint, the query is about 10 times slower then if I supply the query with the bigint directly. for example: SELECT CountryCode FROM Countries WHERE IP_FROM>=dbo.IPToBigInt('194.90.1.5') AN...more >>