Is there a way to specify a composite key when creating a table? the help files aren't helping. I know how to specific primary and foreign keys....more >>

Can I pass a variable for the table name when i use CREATE TABLE DDL statement. If @tblName is declared as a variable of data type varchar and a values is assigned from a cursor, is it possible to create a table by name assigned to this variable. CREATE TABLE [testDB].[dbo].[@tblName] (...more >>

I am trying to use a wildcard in a Variable in the WHERE statement. When I use : Select * from SOP10100 WHERE CSTPONBR LIKE 'SMW%' I get 30 records back which is correct. When I use the code below, I only get 4 records back. Why doesn't the wildcard in the variable work the same way? ...more >>

if I query tables in different instance or differnt server. what factors will affect the performance of the query??...more >>

I have several AS/400 files with fixed length fields that I need to routinely import into SQL Server 2000. What is the best way to import this data into SQL Server? I may be dealing with very large database files, and my understanding is that I cannot FTP directly into SQL Server 2000 table li...more >>

How can I copy all the values from one column into another that are in the same table? ...more >>

I have a client that publishes real estate magazines using Quark Xpress 5.0 (Mac OS 9.1), they currently manually reenter the data in the Quark docs into a Filemaker Pro database for use on the internet. However, Filemaker has it's limitations and I would much rather use SQL 2000 for their inter...more >>

DDL First CREATE TABLE [dbo].[tblMyTable] [item] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS [qty] [float] ) G insert into tblMyTable values ('item1',1 insert into tblMyTable values ('item1',3 insert into tblMyTable values ('item1',1 insert into tblMyTable values ('item1',5 i...more >>

Why the database change this values all the time? ANSI_NULLS QUOTED_IDENTIFIER Some times I don't change anything on the design on the table, and the changes happen any way.... Thanks. Andrew. ...more >>

I am executing the following code in a Execute Sql Task in DTS. SET ANSI_NULLS ,ANSI_PADDING ,ANSI_WARNINGS ,ARITHABORT ,ANSI_NULL_DFLT_ON ,CONCAT_NULL_YIELDS_NULL ,QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF EXECUTE ins_new_user ( this stored procedure inserts into tables...more >>

I am interesting in copying tables from one database to another using VB code. I assume it would be either handled by an SQL statement like a SELECT INTO or it Might be a SQL-DMO code expression. Either way I have not found any good examples to show me how to copy tables from one database to ano...more >>

I'm trying to remove commas from a text in a select statement. It looks like this: Account Description 626900 Travel, Meals, & Entertainment How do I select the description field so the data returned looks like this: Travel Meals & Entertainment Th...more >>

Hi.... I have a doubt about using null values in a where. I have: Select Clasif, count(Clasif) as Amt From MyTable Group by Clasif And i get: Clasif Amt 0 1.200 1 2.300 NULL 3.200 If i do: Select Clasif, count(Clasif) as Amt From ...more >>

I need a VB script that will script all user created database stored procedures and apply them to another database. Thank You, Steven...more >>

Hello Everyone, One of the columns in my table is defined as "datetime" datatype and its default value is set to (getdate()). The format of the values that are being stored in this column are like: 11/14/2003 2:37:21 PM Using Query Analyzer how could i have the results returned to ...more >>

Consider: CREATE PROCEDURE fooOuter AS SET XACT_ABORT ON BEGIN TRANSACTION EXECUTE fooInner COMMIT TRANSACTION RETURN 0 CREATE PROCEDURE fooInner AS /* Pretend to do something */ IF @@PretendBadFlag BEGIN RAISERROR('My pretend someth...more >>

Hi all, I wonder if you could help me with the following queries. Please refer to the business rules below. Thanks in advance. if exists (select * from dbo.sysobjects where id = object_id(N'[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Test] GO CREATE TAB...more >>

I have a table with 5million rows . It has one clustered index on indentity column. This table is a read only purpose table. Count(*) takes almost 10 minutes. when i look at rows in sysindexes it has correct number of rows. what can be causing count(*) to run slow.? Is it advisable to ...more >>

Hello. I'm a flash designer and im using flash MX as a front-end for my web application I am using Coldfusion MX as my connector to the SQL database. Im trying to bring in data in to my Flash application 10 rows at a time. I'm quite new to SQL statements. This is what I have in my Coldfusion ...more >>

Hello, how can I do a { @myVar text OUTPUT } in a Stored Procedure ? I get an error message. Thanks in advance Andreas ...more >>

Hello, I am trying to use the function newId() to randomize the order of rows. When I use the query stand alone, I get two results, which is exactly what I need. When I use the query inside an OR-structure, the number of results isn 't always wrong and not always random. What's the problem...more >>

Hi, I'm using MS SQL 2000 with all updates, and I have the following problem. I'm calling a SP to run a rather large SQL statement, but I need to somehow assign one variable within that SP with a select statment. Here's a shortened version of my SP (Original is 180 lines): --Start-- CR...more >>

For a given column, Qty [int] NULL, is there a significant performance hit if the search condition is written as: WHERE Qty = '1' vs WHERE Qty = 1 I am writing a generic search stored procedure and using option 1 would simply the code. I am using SQL Server 2000. TIA. Dave ...more >>

Hi, I need to create a procedure that will query the master db and find the my database names from a select statement in one of my db tables. And do some like the following. The field 'location' in my lookup table is the same as the db name USE fids GO CREATE PROC upsel_datacollec...more >>

Hello Im trying to insert values with the BCP utility and it stopps when it = violates primary key on table. I was wondering if there is a way to = "make it" skip that row on the file and continue, since I dont want = duplicate values in the table. I'm typing this: bcp database..table in da...more >>

What is the maximum record size in SQL Server 2000? It appears SQL isn't let me store a record larger than 4095 characters. Thanks Roz ...more >>

Hi! I have a table on a sqlserver 2000. A php developer of my company sends a select statement with two parameters in the where clause to that table. These two parameters are the input from one company users on a web page. Then the php developer shows the query result on an an other web page. ...more >>

here's what I'd like to do --- but don't know if I can do - or ho I want to write a trigger that monitors a table, based on the information on the table (an event) I want the trigger to call an external (non SQL) application - say one that deletes a file from the directory.... can this be done? ...more >>

Hi I'm using Delphi with SQL Server 2000 to create Client/Server applications and I've migrated some Paradox files to SQL Server 2000 making the corrects changes in then source, but when a try to edit/post a table in a DataModule from any Form Delphi rises an exception telling me that another user ...more >>

TIA, I'm working with a GovtOffTheShelf ASP application written several years ago. It has been working wonderfully until this morning when I got the following error. Any ideas would be greatly appreciated. Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E21) Multiple-step OL...more >>

How can this be done? create table #a ( col1 varchar(50), col2 varchar(50), col3 varchar(50) ) create table #b ( col1 varchar(50), col2 varchar(50), col3 varchar(50) ) insert into #a values ('abc','def','ghi') insert into #a values ('def','ghi','jkl') insert into #a values ('ghi...more >>

I have a table1 where I have a dateentered and trantype. I need to find the max(dateentered) and then based on trantype join table2 or table3. All data should be in one result. How do I do this?...more >>

Hi All, Does Windows Collation always do 'Word Sort' ?? ('e-mail' will be close to 'email') Is there anyway to make it doesn't ? Thanks, Krist ...more >>

Hello folks! I have a SQL 2000 server with some old jobs, which were created by a maintenance plan, which is now deleted...... I cant get away with them. When I try to delete them I get the following error: "Error 14274: cannot add, update, or delete a job (or it stepsor schedules) that ori...more >>

Hi, I am creating temp table in runtime. The table name is different from every execution. I want to open the cursor using that table name. Is it possible to open the cursor using variable name for table names. Please let me know ASAP. Thanks and Regards Suresh Ponraj ...more >>

Is there a function to extract the decimal portion of a number !! For Ex. 1234.56 Floor(1234.56) = 1234 But I need SomeFn(1234.56) = 56 ...more >>

I have 2 servers (different specced machines eg less fast disks & processor in one) but both running identically specced versions of SQL Server (2k, SP3). I am trying to tune a query against a couple of tables and am checking the stats produced by SET STATISTICS IO ON. The strange thing is, that ...more >>

I wanna design a database log to track down our visitors' behaviors. The log will keep data for the recent two monthes which will be cleaned up by the periodically launched SQL Agent. There will be two copies for logs. Both of them have a same schema. - - - - - - - - - - - - - - - - - - ...more >>

Hi All, This is something that I always knew I'd have to do in SQL queries, but never had the opportunity to learn: I have a query that returns a single column result set that contains the table names I need to use in the 'next' query... How do I do this without resorting to some kind of 'l...more >>

I am a member of the domain administrator and using a database in a server on a daily basis. Yesterday the server was rebooted due to a system failure. After that, when I connect to the database from my workstation via Windows Authentication, I get the following error message: ServerName -...more >>

How do I copy or export database diagrams to another application for printing or display in graphic or HTML format? Thanks Bill ...more >>

Is there a way to return the @@ERROR description instead of the number from a stored procedure? TIA, Grodon...more >>

Hi everybody, I have to store a tree in sql table. I use the solution with a table and two columns: id, parent_id. I need to make an update on a parent and all corresponding childs. Number of levels is undefined. Any idea? thanks a lot, uiranejeb ...more >>

/* I was thinking of building a string and executing it via sp_executesql but because I'm stuffing the output into a table I didn't know if this would work. I'm thinkig it will, not sure of the syntax with the parameters.. So I wanted to consult the experts.... Please help..... */ declar...more >>

Hi im executing a select with option "FOR XML EXPLICIT" SELECT 1 as tag, null as parent, tStoreObjects.sObjectID as [r!1!f1!xml], tStoreObjectPrices.fValue as [r!1!f2!xml], tCurrencies.sName as [r!1!f3!xml], tStoreTemplates.sCode as [r!1!f3!xml], null as [s!2!f1!xml], null as [s!...more >>

I noticed that queries in Scheduled Jobs in SQL Server 2000 run considerable slower than queries in Query Analyzer or Enterprise Manager. I found a Microsoft KB article dealing with the issue. http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q249/...more >>

Hello, I had a problem with a UDF which gets a DateDiff between 2 Date Arguments that are passed to it from an sp Declare @m int Set @m = DateDiff(n, D1, D2) Someone had entered a year of 7003, obviously a typo. So I need to check If Year(D1) <= Year(GetDate()) This not working...more >>

I have a stored procedure that begins as follows: CREATE PROCEDURE SPENDING_S_Level1ByExpenseCategory @datStart datetime, @datEnd datetime, @intOrganizationOUID int, @intOUID int, @curTotalSpend money, @bitConsolidate bit as .... When I run it in Query Analyzer, it takes about 2 second...more >>

I am running into a problem with SQL Server 2000 not properly validating syntax when I alter a Stored Producer in Query Analyzer/Enterprise Manager. The Stored Producer was about 1000 lines with comments, yesterday the syntax checker missed an incomplete IF BEGIN END statement, causing the...more >>

Hello, I have 2 dates and need to exclude all business and Holiday? What is the best approach in doing so? Is there an example that I can take a look at. Please help ...more >>

Can i join tables from different database (both SQL database)? Thanks ...more >>

Hi all, We have stored procedure, which basically strips a header and footer of a table (first and last line). The procedure is written like so: CREATE procedure stage.usp_StripSMMT as declare @count int declare @smmt_count int declare @check char(3) select @count = (select count(*) ...more >>

Hi, I need to build a query to get intersection of data of an arbitrary number of sets. I'd like to find a single query that will fit the need for any number of sets. Let's say I have a table named Test which is defined like this: CREATE TABLE [Test] ( [Field1] [int] NOT NULL , [Fiel...more >>

Does anybody understand why a delete operation of a single indexed row may take 40 seconds per delete in a database table consisting of 18 million records? The table itself is 18 million and the stored proc does the following: delete from storage_folders where sfo_folder_guid = @guid s...more >>

Hi all, I have a problem with as sql query and could desperately do with some help. I have three tables, linked as follows Jobs Job_Update Update_Reasons JOB_REF JOB_REF UPDATE_ID UPDATE_ID I need to import a lot of jobs in the database. normally what these are e...more >>

I posted something similar to below in response to a previous thread I started (see link). I guess the original post was so far down that no-one saw it. Either that or no-one can help me. ;-) I got some responses to my original post, but unfortunately I did not state the problem correctly. ht...more >>

There has to be an easy way to accomplish this and I hoping someone could enlighten me. I'm using SQL Server2000 and have two tables: Products_T: Product_ID Product_Name Product_Description Product_Sizes_T Product_Size_ID Product_ID Unit_Size Price Obv...more >>