Here is my DDL: create table person( UPN char(13) not null primary key check (ucn between '0000000000000' and '9999999999999'), personal_name varchar(40) not null, sex char(1) not null check(sex in ('M', 'F')), date_of_birth date not null ) insert into person(UPN, personal_...more >>

All, Should it work in MSSQL Server 2000? Just D. Description Hides the source text for the specified compiled object. Syntax sp_hidetext [objname [, tabname [, username]]] Parameters objname specifies the compiled object for which to hide the source text. tabname specifie...more >>

Hi, I have a database with full recovery model. The problem is that I have a table in this database which is intensively use for a search and my transaction log grows really fast. This table doesn't need to be recovered, it holds only temporary data. I tought about putting this table in anoth...more >>

good morning to all as i am new to t-sql progrmming always need help from newsgroup. Now i have this query SELECT x.iss_no, x.pro_name, x.distrib, x.dis_dat, sum(ret_qty) AS unsold FROM (SELECT iss_no, ...more >>

Query optimizer should depend in part on relevant data. Now given two DML commands S1 and S2, S1 inserts certain data, which is relevant to S2, S2 has 2 where-clause conditions, say where C1 and C2. It seems that if we execute them as S1 go S2 Then C1 would be evaluated first; But i...more >>

Hi, As a consultant, what is the average dollars per hour for database Administrator/database programmer who has 4 years experiences? is $70/hour good or bad? ...more >>

Hi all gurus I am using this news group , and its very helpful to me. Now i want to know that is there any news group for Oracle and what is its server name. Thnaks and Regards Amish ...more >>

I deleted the sample DB pubs on my home machine :( I was fooling around with named transactions and error handling, and of course my DB went away. In any case I would like to recreate it fresh. I remember once seeing somewhere the create script plus insert statements for recreating the beas...more >>

I'm using ODBC to access an SQL Server 7 database in which I have some fields of Data Type Bit. In my VBScript ASP scripts I'm inserting values true and false in these fields which results in an error. If I change my scripts to insert 1 and 0 then all is well. Can anyone help me understand what'...more >>

Here's my code: CREATE PROCEDURE [dbo].[sp_CheckNetName] @CID as int, @Netname as nvarchar(25), @Return as nvarchar(5) OUTPUT AS if exists(SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname)) SELECT @Return = 'yes' ELSE SELECT @Ret...more >>

Running MSSQL2000 with a linked server to another MSSQL2000 box in order to call a complicated SP. Within a local SP, I need to get the results from the remote SP, put them in a temp table, and work with that data set. In the local SP, I create the temp table and then: Set @freeSql = '...more >>

Hi, Can any one tell me the syntax for this? As soon as the union statement becomes a sub query it gets bitter and twisted about the order by clause. I have tried moving the order by out to the top level. No Joy. The union statement out of the sproc is: select scheduled_read_date as read_date...more >>

Hello, I would like to know I can script the following: Update Field1 to current date Read Field1 and generate a unique 4-character random string of capital letters or numbers, and write that to Field2 in a 1-record table that is updated daily after midnight. Thanks!...more >>

I have stored procedure. When the user clicks on the asp page, the connection execute this procedure. In my procedure I first delete all values from the table and then calculate and insert the new one. DELETE from tableName INSERT into tableName(.....) On the end, the new values are sh...more >>

I'm trying to do 'select * into <some database>..<some table> from <another table>' multiple times where the table names vary each time. I get an error "Internal Query Processor Error: The query processor encountered an unexpected error during execution." This happens whether I run the querie...more >>

Hi, I am trying to optimize queries on a big table (more that 50 million row) I am thinking to turn of statistics (sp_dboption 'monitordb','auto update statistics', 'off') and instruct query optimizer to use specific index like: Select * from BigTable (nolock, index=idx_key) wh...more >>

I have been trying to trace a restore that I kick off through EM. When I look at the trace data, I dont see anything related to the restore. I bascially wanted to know what the statement that is passed to SQL Server when I do a typical restore. Thanks....more >>

I can tell programmatically if a column is computed. But is there a way of telling whether a data column participates in a computed column? Thanks. Neil ...more >>

Please, explain me, why INSERT / UPDATE / DELETE with global tables and EXEC are prohibit in the user-defined function? ...more >>

Good morning to All I have this query SELECT x.iss_no, x.pro_name, x.distrib, x.dis_dat, sum(ret_qty) AS unsold FROM (SELECT iss_no, pro_name, distribution.pro_cod, ...more >>

Does anyone know how I can select the data from the old users table and insert it into the two new tables? I know how to use insert with a select statemnt to insert the users data from the old table to the new. What I don't know how to do is get the login data copied into the UserLogin table...more >>

Hi, I have two tables. One table has the primary key "id" (I didn't create it), which is unique. Each row is a contact record. The second table has four fields. one field is the "id", but may be duplicated. second field is called "primarykey" and is just that and unique. third fi...more >>

In oracle I use to do query like this SELECT 'AAAA' AS AA, 'BBBB' AS BB FROM DUAL UNION ALL SELECT AA, BB FROM MyTABLE first line just gives me some fixed values not coming form any table. DUAL for oracle is a sort of dummy table. Is there anything like this in SQL SERVER? Thanks you all ...more >>

For distributed query perfomance what would you select SQL server or OLE DB provider for SQL server ...more >>

When using Like in your Where clause, are Indexes ignored? Currently we only have a couple hundred records in a column(name), but the potential for millions is there. The app currently uses Like all the time, even if the full name is given. What performance issues could this cause in the fut...more >>

in my application, i use SQL-DMO to access database objects. now i can access 'column name', 'data type', ...But i don't know how to access the 'column description' string which i inputed in the 'table design' window. could you tell me which object/property should i use? thank you in adva...more >>

Can I specify the server name in a USE clause? I would like to do something like this: Use ServerName.DBName But I get an error that says "ServerName" can't be found in sysdatabases... Is there any way to specify the server name and database name to use in a SQL stmt? Thanks ...more >>

Hi, SQL 2000 is there any way to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE for all transations , just as a default isolation level. I know default is READ COMMITED, but how can I change it ? Is the ISOLATION LEVEL session related? Thanks, Pat ...more >>

What is the best practice when designing tables such as those used for company profiles where the companies exist in a single state such as Wisconsin? There are issues that are relevant to normalization. For example, should there be separate tables to store a list of cities and counties which...more >>

This will either be a Crystal Rpt, Reporting Svc's or ASP page depending on ease of rendering. I want to end up with... Prob1 ,Prob25.... MarketCenter ,CoA ,CoZ MarketCenter ,CoB ,CoQ MarketCenter ,CoL ,CoN MarketCenter ,CoT ,CoT (same co can have more than one Pro...more >>

Hey all, background: running queries from MSQuery against SQL 2K database customer table has 18,000 unique records DupeRecs table has 10,000 unique records InvitationNum is nvarchar(11) [Cust PO No] is nvarchar(255) I run this query to determine which customer rec...more >>

Dear all, I hate this kind of things. I'm trying to obtain just a 300 rows from one table which own 890,999,999 rows from our dear Query Analyzer and i'm waiting a response for long so this way, finally, i'm going upstair to take a coffee. it never works! Fucking life! Thanks in ad...more >>

SQL 2000 im trying to write a stored proc to return if a clients account is over limit with current transaction they are doing. There are several limit periods which get applied. I first start off by getting the 1st week of the current month that they have submitted and Set all Limit period...more >>

I have the following VB program that runs on a web server. What it does is to run a timer, waiting for a file to appear in the folder it checks (the file is output of a SAP job on another domain). What I would like to do is to convert this VB code into a SQL DTS job that would sit & wait for ...more >>

so here is the scenario: i have around 15 different stored procedures in my database that do updates and inserts into this one table - (tTableA) - i know - this is extremely poor design. right now i don't have time to correct the design problem - but i need to add a message (basically stating...more >>

Could someone please explain what this is doing? I can't find any documentation on this, but am working on a procedure written by someone else. SELECT dconfig.JobRouteJob , dconfig.JobrouteSuffix , dconfig.JobrouteOperNum , dconfig.FeatureDisplayQty , dconfig.FeatureDisplayUM ...more >>

Hi, I have a phone table and an address table with the join being the customer. I have badly formatted international phone numbers in the phone table. I would like to do an update on the phone field that will format the international phone numbers like this. NN-NN-NNNNN... I put some sc...more >>

Hi, Is there function in sql 7 that Return starting point of character in character string (from left)? Like InstrRev in VB. Thank you in advance. Shailesh ...more >>

The optimizer is doing table scan instead of using an index.If I directly use the dates instead of using variables than optimiser is using index.Is there a bug? I am using sql server 6.5.Any help would be greatly appreciated. /**********Query**************/ --optimizer using index SELEC...more >>

I have the query below, giving me the "calculatedprice" for one " invoice_id ". I have inserted a new field in the invoice table where I want to update the "calculatedprice" for every "invoice_id" in the table. How is that done? SELECT SUM((unitprice * (unit_qty /factor)) - ((unitprice * (unit_...more >>

I have a database with Employee information in it. I am trying to create a view that will display a count of how many employees are in each department. I know how to do this for one dept, but not how to do it for a few depts. For instance, SELECT COUNT(*) FROM Employee E INNER JOIN Position ...more >>

How can I use Getdate() function to calculate the time 45 minutes ago within a SQL statement? Select * from Process_Schedule where StartTime Between GetDate() and GetDate() - 45 minutes Thanks, Mark ...more >>

Hi I have an idc/htx error The code was perfectly working on NT4 server, but when migrated to 2003 server, It kept getting errors. I have a test.idc testhtx file need to execute SQL server store procedure Test.idc: Datasource: DSN_ APP Username: user_1 Password: password Expires: 1...more >>

I have a table from an old database that I would like to naturalize and move into our new database. The old table has 7 columns. The first 4 may contain duplicate data. Here is an example. Customer Sort Program Package Code1 Code2 Code3 99999 1 1 A ...more >>

Hallo, this is my first Post here. I hope I do all right :-) And sorry for my bad English *smile* Simply problem: I would like insert a text to the database. This text have more than 1000 chars. I use the data type "Text" for the field but when i insert the text, the database cut the tex...more >>

Can anyone point me in the direction of a good summary of the differences between T-SQL and PL/SQL? Thanks Damien...more >>

is there any SQL that can be fired off against Access that will cause it to apply a default to a particular column? using alter table mytable add constraint myconstraint default (0) for mycolumn doesn't seem to work, either over ADO or within Access. Any ideas?...more >>

Hi, I keep getting the following message when try and use a stored procedure. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. Here is the stored procedure: ---------------------------...more >>

Hello again, How can I see which Indexes used for a query to run beyond execution plan? Thanks in advance....more >>

Hello Experts, Does SQL Server uses Unique Indexes to optimize performance of queries? Thanks in advance...more >>

Hi all, I wanted to get some values from several columns in a same row. What I did is this: SET @field1 = (SELECT field1 FROM mytable WHERE pk = @the_pk) SET @field2 = (SELECT field2 FROM mytable WHERE pk = @the_pk) SET @field3 = (SELECT field3 FROM mytable WHERE pk = @the_pk) I wonder if ...more >>

Hi, I'm sorry to be a pain (first time on this site), I'm trying to run a query against a table which has 10 columns. I'm trying to pick up the reference numbers and the date fields where the date is after 15/01/2001. Both of the fields are varchar(255) on the table. There is also a likelyh...more >>