Hi, I rebuilt a database from backup, most of the objects owned by a user, I would like to change the objects owned by dbo. is there a easy way to change them all? or i have to do one by one? Thanks...more >>

Hi, I want to display the results of the following query in my program. select a.cdecode as cdecode, a.cdename as cdename, a.cdecallid as cdecallid, a.cderecID as cderecid, b.cdecode as bcdecode, b.cdename as bcdename, b.cdecallid as bcdecallid, b.cderecid as bcderecid from shrcodemaster a...more >>

I need to combine multible string records into one record based on a common reference number. ie. one reference number has multible string entries. Is there an aggregate function to stich the strings records togather? Is there an alternative?...more >>

I would like to create an array or collection of 1 to 300 values and store the result in a field of a row in a table in a database. The array would have 1 to 5 elements. How? Thanks in advance. ...more >>

What I am trying to do is to replace the null value in a fields with a value say '999' and I am having trouble figuring out the syntax can anyone help please. I have a table that is continually updated from a master source and sometimes the 'code' field is blank and as a result the data d...more >>

How can I insert/import or copy char or nvarchar to numeric ? Thanks. ...more >>

Hi, I'm writing a really simple test to validate email addresses. To do this I was hoping to use a statement similar to the one below to identify any email addresses not containing valid characters. select * from emailaddresses where emailaddress like '%[^a-z,A-Z,0-9,_,-,.,@]%' The scr...more >>

Hi, We have two tables: Table1: create table t1(t1_id int, t1_name char(50), t1_ref_to_t2 text) with index on t1_id: and Table2: create table t2(t2_id int, t2_name char(50)) with index on t2_id Suppose these tables have the following data: insert into t1 values(1, 'a', '2,...more >>

When I submit the following TSQL to SqlServer 2000/SP3: INSERT dispinfo (datetime,incno,alarm,units,loc,type,insType) VALUES ("9/29/2003 01:36","F041069908","1","AU32","3052 NW 134 ST #2 ,SEA","AFA (AUTO. FIRE ALARM)",2) it fails with the following error: Server: Msg 128, Level 15, State ...more >>

I have a table in SQL Server with around 20000 records. It has a integer column and a index on that column. Only when a certain integer value (145) is inserted in to the table the following query takes a very long time to execute with Processor going up to 100% Select * from MyTable Where My...more >>

Hello I was wondering if there is a an inbuilt sp that rebuilds the fulltextindex of a table. if so, how? TIA /Lasse ...more >>

Hi, Quick question, I have two tables, orders and customers. The relationship between the two was previously by the customer name. In the interests of data integrity I have decided to replace this by creating a customer ID field oin the two tables (PK in Customers, SK in orders) and using t...more >>

Hello, I am getting a very strange error. I am calling a stored procedure via a dynamic exec statement. The output from the called procedure indicates that it is getting the input params just fine... and does the expected actions. However, I keep getting an error that claims that the parame...more >>

Not having tried this before, at first I thought it would be simple to have part of the table in one order, and the other part in some other order. SELECT * FROM ABC WHERE GROUP=1 ORDER BY ALPHA,DELTA,BAKER UNION SELECT * FROM ABC WHERE GROUP<>1 ORDER BY ...more >>

Hi, I have a trigger owned by user1, I would like to change it to owned by dbo. when I doing sp_changeobjectowner 'user1.tI_table1', 'dbo' or sp_changeobjectowner 'tI_table1', 'dbo' it says Object 'tU_table1' does not exist or is not a valid object for this operation. how to chan...more >>

is there a way to run a join statement and have it return the values that do not match? I'm looking to report on the exceptions, and not the similarities, of the table contents. Thanks! ...more >>

I have a program that writes values to fields in a SQL Server DB. I need to send the CTRL-0 value to some of the fields. Does anyone know a way to do that? I have tried: rsRecordset!Field0 = SendKeys("^0") and rsRecordset!Field0 = vbKeyControl + vbKey 0 to no avail. The former doesn't work ...more >>

Hello, Was hopoing somebody could help me out with an update of a table column i need to do. The column I need to update is a character type column and it contains a description. Within the field at times users have entered a "(double quote) mark and I need to remove these quotes. I either w...more >>

Hi All I have the following join, which shows me how many candidates have each particular employment status. However, my employment status table has a column (OrderID) to order it by each stage of employment. When I say "Order By EmploymentStatus.OrderID" I get an error, saying it must be cont...more >>

Hi! I have a feeling this is something of a classic problem, but I have not been able to find a reasonable solution. All ideas are welcome! What I want to do is a way of identifying all modified columns in an update trigger. Then I want to return all the new modified values and their column ...more >>

Is there an equivalent book to "Design Patterns" by Gamma... for database design? I don't want an introduction book that explains joins and normalization. Is the book "Data Model Patterns: Conventions of Thought" by David Hay any good?...more >>

I am trying to find a way to monitor whether my queries are hitting cached execution plans or not when using sp_executesql. Is there a tool for monitoring hit rate or actual hits of cached execution plans? Thanks in advance! Jiho ...more >>

I was trying to update a record from a transaction and simultaneously trying to read the record. Both the transactions were set to the default ReadComitted Isolation level. Now I found that the Read blocked, even after the other transaction had finished its update operation(of course, not commit...more >>

Tks for you reply. I got to there... but how can I get into the order that I want. JFB >-----Original Message----- >Use the IDENTITY() function, not to be confused with the >IDENTITY property: > >select IDENTITY(int, 1, 1) AS IDCol, > '444' AS rectype, > * > into ... > > >HTH...more >>

Hi SQL Gurus, I have to create a VIEW for ALL Parent-Child tables in our projects (quite many of them), where I have to SELECT ALL COLUMNS of the parent and child and also Left Join to a Reference Table. While each parent and child has many colums it self, is there any tool / wizard that ca...more >>

What are the valid field types/sizes for variable declarations in a tsql script? Any recomendations for documentation - I'm completely new to TSQL but have some experience with PL/SQL. Thanks....more >>

Where do I find the Product Code for the instance of SQL Server. Like in Word we go to Help-->About Microsoft Word its shows the Product ID. How do find this in SQL Server 2000 ? Thanks in advance!...more >>

Hello all, I am having trouble finding samples where SMO is accessed from unmanaged(ATL) based C++. Can some one please help me with the following: 1. The correct #import to implemment. 2. I have been using _SQLServerPtr and _SQLServer2Ptr for DMO. What replaces it? Any help will be ...more >>

Hello again! Well here is another question: Is it possible for a user to define a function that makes a comma-separated list from a column of string values? For example, create a function called dbo.MakeCSV() and a table with one varchar column TheCol ------- ...more >>

How to find the missing seq_num Table EMP, column emp_name, here are the sample data emp_name --------------- SWjohn001 SWpaul002 SWkevin005 APdavid001 APsherry003 APmike005 APpaul008 DVpatty001 DVkitty002 DVmitty006 First two characters in 'emp_name' identifies the 'group' an...more >>

This trigger needs to fire on inserts and updates. I would like to create a simple trigger that will insert or update the present date and username (that updated or inserted the record) that corresponds to the record in a table. I would like this trigger to be able to handle multiple rec...more >>

I create a table using dynamic query in a stored procedure. When I select from the newly created query in the next step, I get "Object not found" error. This behavior is not consistent. This error occurs on a SQL Server 2000 Enterprise Edition (SP3). Any help is appreciated. Thanks, ...more >>

Hi I have a Stored proc that will log all sql errors it gets to a table. so, after each statement I check @@error. How do I stop the error from still getting raised in the application that calls the stored proc, e.g. query anal or .asp front end ? This is what I do in my SP: -------...more >>

HEllo, I asked a question yesterday about being able to run a query in SQL from say a batch file. I got an answer of putting it into a command but I think i am still doing something wrong because I get this message. osql -S:bscar -D:cepa_existing -U:sa -P: - I "c:\temp\phase_disabler.sq...more >>

All, I found this serious bug with the IN clause in SQL 2000 select * from MAIL_TABLE m where m.server_id in ( select server_id from DELETED_SERVER ) The above gave me a list of all MAIL_TABLE records with the server_id_s NOT IN the DELETED_SERVER table. Why? Note that I have ...more >>

Hi, I'm trying to build a query returning fields sorted by their title, but I want to add "(new)" title to the top of the list. I've built the following query that do such thing: CREATE VIEW dbo.List AS SELECT TOP 100 PERCENT id, title FROM (SELECT 0 AS order, NULL AS id, ...more >>

I'm using COM Object in a stored proc. I'm able to create the object using the sp_oacreate proc. but when I try to exec. method of the COM obj. using sp_oamethod, I'm not getting any results nor errors after a very long time, i.e. control is not coming back, it keeps executing. Any help...more >>

Hi SQL Gurus, I am trying to use Partitioned View and just found that it can only updated using Instead Of Trigger. (below is DDL). If Not, I will get error : " UNION ALL view 'view1' is not updatable because a partitioning column was not found. " Is InsteadOfTrigger the only way ? or the...more >>

The following trigger executes properly via an application on insert, update, and delete for myself. If another user using the same application performs the same transaction as me, the user receives the Database error: [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries r...more >>

I am puzzled as why SQL will not acknowledge the UPPER function. I am using the function to search for a value in a column ONLY when it contains like 'EC' in upper case. However, SQL will also accept when it is in lower case. How can I make it to accept upper case only? Thanks advance f...more >>

Hi everyone. I have a table with only an ID column and a Filename column (varchar). The way I would like to set things up is the Filename is actually built from the ID. So for example, two rows might look like ID Filename -- --------- 43 MyFile43.zip 298 MyFil...more >>

First some DDL. CREATE TABLE Vouchers (VoucherID INT IDENTITY(1, 1) PRIMARY KEY, VValue MONEY) Dead simple table and the idea behind what goes into it is pretty easy too but I can't for the life of me figure it out without resorting to cursors (YUK). So, the idea is that the customer wa...more >>

Hi I have a Stored proc that will log all sql errors it gets to a table. so, after each statement I check @@error. How do I stop the error from still getting raised in the application that calls the stored proc, e.g. query anal or .asp front end ? This is what I do in my SP: -------...more >>

I'm trying to compare databases across servers. Someone from this group pointed me to a stored proc called sp_comparedb. However when I try and run it I get the following error. Where do I set the ansi_nulls/warnings on? I tried doing it in the QA window I was using, but still get the same...more >>

Hi there, Sorry fot not posting the entire ddl, it is too large/complex. I have stripped down my nested views/triggers problem to the following: Tables: - Table1.PK - Table1.Col -- Table2.PK -- Table2.Col --- Table3.PK --- Table3.Col Views: View1 = SELECT * FROM Table1 View2 = SELE...more >>

Can someone show me how to write this query to get a result that does not allow duplicates in the EMAIL column ? From what I read and tried, DISTINCT can only be used when the Column you want distnict is the only column selected. I do not know another way to do this. "SELECT Email,FirstName,...more >>

a) Is it possible to use a view, and within that view to call a stored procedure that does calculations? b) Or is it possible to create a sql statement that has in the select or something a stored procedure that does calculations? Thanks! p.s. i already posted this but for some reason,...more >>

Which makes more sense, to drop the indexes, load the data then create indexes or load data without dropping indexes. I have a job that every night populates a table from multiple tables. Population takes about 15 minutes without indexes, but creating indexes will take a long time too. If ...more >>

Hi, Is there any built-in functions or Stored Procedures to get the owner of the object. Here its a bit tricky, I am planning to get the owner of the Stored procedure(Object) which I am creating. The code inside my SP should get this SP owner name who is creating it. Is it possible. ...more >>

Hi What is the max length for a varchar output parameter for a stored procedure in sql 2000 ? Thanks !! Tania...more >>

Hi, I had an argument reagrding alter proc vs drop&create proc. Whic is the recemmoded way and why? I read some where alter is the recommonded way but some argue that alter proc is not and SQL maintain versions if you use alter is this is right? Please some one through some light on adv and...more >>

Please help me with an UPDATE SQL statement. Thanks. I have a table with two cloumns: Name Status ---- ------ A New B New C <Null> A <Null> I need to write a SQL to update the rows where "Status" is <Null>, i.e. the last two rows. The "Status" column should have the...more >>

I have 2 tables:Table1 and Table2 where I have ID and Dateentered. The both tables can have the same id but different date. If record in table 1 is oldest then in table2 select data in table1. How do I do this?...more >>

I have to discover which table are referenced in each page of site with a lot of pages. The problem: there are nested procedures and when I try discover the dependence from the tables to the procedures called in the page I can't because this message during the object creation: "Msg 2007: C...more >>

Need some help. I have a table that receives inserts in batches of 5 rows at a time. Any one of these rows may generate a primary key violation. I want to write a trigger that will process each row and if a primary key error occurs I want to just acknowledge it with a message and then conti...more >>

Microsoft Consumer this is the latest version of security update, the "January 1998, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to mai...more >>

Hi All, Just after some help with a bit of code, I am using the following code to back up one of our databases to a drive on a machine across a network BACKUP DATABASE [Leads] TO DISK = N'\\MYMACHINE\d$\backup\leads.bak' WITH INIT , NOUNLOAD , NAME = N'Leads backup2', SKIP , STATS = 1...more >>

Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protect ...more >>

Microsoft User this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to continu...more >>

Hi I have a stored proc that creates a tmp table is there anyway I can specify the collation type for the table to build in my stored proc Thanks for any help Shau...more >>

Dear Friends I have created a followin query to get the results from two tables of diffrent divisions in both the tables there are 1800 records but the union query is giving me 1300 records instead of 3600 records please suggest. CREATE VIEW dbo.VesselListUnion AS SELECT dbo.V...more >>

Can we access a cube in Analysis Service from T-SQL? ...more >>

In SQL Server 2000, Could I use transact-SQL statements to perform file operations, such as open/close, read/write a file? ...more >>