I am having a problem creating a custered index on a materialized view. Below is three tables and a materialized view: CREATE TABLE dbo.Product ( ID int IDENTITY (1, 1) NOT NULL , Product_Desc varchar (64) NOT NULL ) ON PRIMARY GO CREATE TABLE dbo.Product_Value ( Product_Product_Valu...more >>

Hi , How can I get a list of all tables that has page \ pages in the cache right now (snapshot of the cache for tables not proc ? ) Thanks, Eyal ...more >>

Hi, I've got a table with names of clients, the column is of type nvarchar(100), they've asked us to exclude any client whose name starts with a number, such as 1abc or 1 abc, just as long as the first character is a number, exclude. Can someone let me know how to do this? Thank you. ...more >>

Hi, I am in need of knowing which type of replication is the best to use in SQL server. My application runs at the server place and there are client processes running in remote which gets connected to this server process . Only the server application updates the DB. Clients ac...more >>

Hi I'm writing an app to generate some SQL. Part of the app wraps certain SQL functions Ideally, I'm trying to get a nested CASE WHEN to return a true/false which in turn feeds an 'outer' CASE WHEN. Is this possible? For example, consider the following SQL SELECT CustID, Da...more >>

Hello I have a table 'purchases' as follows: customerid (Number) trackid (text) date (date/time) available (number) invoice (number) A sample of data with fields in the order above may be: (3, '001M', 01/07/2006, 0, 3) (8, '002M', 12/07/2006, 1, 5) The value of "available" will o...more >>

Currently users can enter a word to search records in the database and I use the IndexOf method to find the rows. I am now looking for a way that the row will be found even if the user misspelled one letter Thank you, Samuel ...more >>

Hi, I would like to use a GOTO concatenated with an input variable to avoid a lot of IF..THEN The concatenation below seems to work, but it doesn't find the mark however. use pubs GO DECLARE @s AS char(5) SET @s = 'BV123' exec ('GOTO ' + @s) print 'before' BV123: print 'after' ...more >>

I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that sql server resides on is not reachable. The error is different depending on the connection string tha...more >>

Hi, I am using SQL Server 2005 and am attempting to update a value in a table column using the code below: UPDATE TABLE A SET COLUMN_1 = COUNT(COLUMN_X) FROM TABLE B WHERE SOMETHING = SOMETHING ELSE SQL Server gives me an error message that says you cannot UPDATE with ...more >>

I have a dataset that is in the following format ID | Title |CatID | Template 1 |Title 1 | 1 | 1 2 |Title 1 | 1 | 2 3 |Title 2 | 1 | 2 4 |Title 3 | 1 | 2 5 |Title 4 | 1 | 1 6 |Title 2 | 1 | 5 Any Ideas? i would like to return a r...more >>

I have a less than ideal data entry situation. I have 5 available fields for email addresses and I need to create an email list that will potential have more than 5 addresses. So one field can have multiple data items. I can enforce the following data entry restrictions. The fields must be fil...more >>

I am passing the SP 2 vars. @id = 1 @buyerslist = 3,5,7 I need to loop threw the comma seperate buyerslist and insert into a table for loop Insert into table (id, buyerlistid) VALUES (1,3) next ...more >>

I'm having a difficulty spinning this one in my head. I have a list of parts in orders. The fields I am looking at are: ordernumber, status A sample data set is like ordernumber status 1000 SH 1000 SH 1001 SH 1001 RT 100...more >>

I need to nest a cursor that fetches values from database A, inside another cursor that fetches values from database B. Is that allowable? How would I do that? Do I just issue a USE [dbname] statement as part of each cursor? Will that slow things down a lot? Thanks....more >>

My boss is looking for pros/cons in going from SQL Server 2000 to 2005. Does anyone have anything already that helped you sell your boss? Thanks, Ron ...more >>

Hello, Is it possible to create a list of table names and the value from one of the columns within those tables. The <field1> column has one distinct value per table result would look something like this: tbl_name field1 (of each table) table1 value table2 value ...more >>

I have a simple table tblNotes. NoteID, ContactID, ContactTypeID, EnterDate, Note If ContactTypeID is 1, then ContactID must be one of the ID's from tblClients (ClientID) If ContactTypeID is 2, then ContactID must be one of the ID's from tblVendors (VendorID) If ContactTypeID is 3, then ...more >>

Hello, We have changed columns in two tables from varcahr(8000) to Text. One table is used to update the other so they both have the same Text data type column. This code fails the syntax check with the message: 306: The text, ntext, or image data types cannot be compared or sorted when usi...more >>

Hi all, I've been assigned the job of creating a vendor cost report. I've created a temp table to populate with our iem numbers and the vendors we buy from as below with some simple sample data. Each unique item_no can have up to 3 vendor_no's. Item_no Counter Vendor_no Co...more >>

Hello Everybody, I am trying to update one column value of one table with the other table's column. my other table is link server's table. update dbo.table1 set suspid = b.suspid from dbo.timedetail a, linkservername.dbname.dbo.table2 b where a.id = b.id I am getting following messag...more >>

I need to write a query that simply returns the counts of the Email column in tblPerson where it is null versus where it is not null in the same query. I need something that takes LabOrderNbr as input in the form of a range -- for example getting a single count is easy: Select Count(perso...more >>

SELECT * INTO ThisTable FROM ThatTable fails on databaseA but not on databaseB. What do I need to look for permission-wise? Chris Macleod...more >>

Concatenating and ordering at the same time ... So we have two tables authors with a list of all our authors names, details in 3 languages l_publication_author which links our books with the authors what we want is to display the authors as a string (concatenated) but order asc in the langu...more >>

Hi, I have a stored proc that accepts many parameters to be used on the Where clause. If they come in with a value, I use them with the COALESCE, ie: AND ohPO = COALESCE (NULLIF (@PO, ''), ohPO) I have several AND statements like this. But I want to only pass in a part of the PO and have ...more >>

I'm setting up a largish script to do a refactoring of a database. I have a cmd file that uses osql to call the sql files I need to execute and I use the "-o filename" syntax to logprogress through each file. It appears that whenever a "go" is encountered, the first thing that happens is that...more >>

Hi I have an asp app accessing a SQL Server db and am getting myself into a tangle over dates in a query. I have a date field ValidFromDt (smalldatetime) and another date field ValidUntilDt which respectively define when a record is valid from and until. I want to select all records valid on ...more >>

Greetings all - I have a table with a trigger (for update) to set the DateModified field when a change if made - simple enough. I also have a trigger (after update) to save the history in to a table. The problem is as follows: Update a field in the table and I get an insert into the histo...more >>

hai I've a shift allocation table where i can store employeeid,shiftid, effective from date, and effectiveto date. an employee may have 3 or more shifts in a month. the shift details are stored in the table. the data will be in the form of employeeid shiftid effectivefromdate effectivetod...more >>

hi, i have two tables in same field name. how to intersect (unmatched) find between two tabels. by vinci ...more >>

Hi, Can I have a query where aggregate function is inside select statement and is not part of the aggregate funtion. e.g. select tab1.value_id, (Select sum(tab2.data_name) FROM v_CalcValues WHERE AND par1 = '1' And [date] > '1/1/1900' And [Date] < '31/12/2100') as tab2.Value, F...more >>

I'm trying to get data for business submitted from one business day to another lets's say from yesterday 07:00 until today 06:59?? i have Select created_date where created_date < Getdate () and created_date > Getdate () -1 i get nothing back? i need to have data between yesterday f...more >>

Hi everyone, I have a question I have a simple table in my SQL-2000 database which is used to store documents in the database. The documents are stored in an image field. Now the problem I stored about 3300 records and the documents stored are about 100 mb. But the table uses a lot more spa...more >>

Hi there, I have a select statement SELECT ORIGNUMB,SOPNUMBE FROM SOP30200 (NOLOCK) WHERE SOPNUMBE='ORD_1C_0000007927' OR SOPNUMBE='ORD_1C_0000008154' OR SOPNUMBE='ORD_1C_0000008352' OR SOPNUMBE='ORD_1C_0000008195' Result: ORIGNUMB SOPNUMBE O-0000095897.1 ORD_1C_...more >>

I want to save the changes in a table. For example, if ColA was changed, I want to write a row to another table that describes the change. Psuedo code is like that: (assume InsertLog creates the row) CREATE TRIGGER tr ON MyTable FOR UPDATE For Each Column in Inserted if Inserted.Column.Va...more >>

Hello, I am learning SQL Server 2005 and .NET 2.0. Please help to create user defined type in SQL Server. I have written a type in assembly DemoSQLServer: namespace DemoSQLServer { [Serializable] [StructLayout(LayoutKind.Sequential)] [SqlUserDefinedType(Format.Native, Name="Sex...more >>

Hi=20 One of my colleagues wrote some code to execute a stored procedure and = put the results into a temp table. The stored procedure in question = selects data from the current database and from a view on a remote = server (via linked server). In our development environment, the linked = se...more >>

Hi, Can anyone recomend a tool for data generation (loading) for SQL Server 2000 database which takes into consideration DRI, identity columns and triggers. Thankful in advance Saso. ...more >>

I have 2 databases .. One is saat the otherone is Xml They both have identical tables and fields.. DBNAME:Saat TableName:urunler fields urunadi fiyat DBNAME:Xml TableName:TBL_PRODUCTS fields urunadi fiyat I want to update fiyat field on XML named DATABASE. Update X...more >>

Hi, Currently when i run the following scripts (Tran 1, Tran 2, & Tran 3) = simultaneouly, the Tran 2 & 3 will hang and fall in waiting status = because of being locked by=20 Tran 1. How could i make use of row level locking where i want Tran 2 to = be updated successfully (different row) And...more >>