Hi All, We have a big table with about 35 columns - 10 Million records. The table has got indexes on certain key columns only. Currently we need to create few reports using this table. The filters / search criteria for the new reports uses columns which are not indexed previously. I...more >>

create #table(A nvarchar(30), B nvarchar(20), c nvarchar(15), D int) insert into #table select a,b,c,d from x,y,z where c like 'Norm%' group by a,b,c,d create #table(class,name, exam,score) as: A name1 math 100 A name1 math 88 A name1 Phy 98 A name1 Chm 98 A name1 SPT 89 A name2 ma...more >>

As the subject. Thanks bead...more >>

I'm having problem with SQL Database that getting bigger: 1. I have a batch processes that were enclosed with one BEGIN...END TRANSACTION and this perfectly run smoothly in a small records until the database grows up to 800MB the problems occured, where some of the records where not updated ...more >>

What's the best typical fill factor??? Most of ours are set to 0 (the default) but some are set to 80% ...more >>

In my C# code I need to provide the list of all available SQL servers on the interface. I do I get that? thanks. bead...more >>

Hello, I want to have an integer that is 64K, but no negitives? Smallint will do -32000 to 32000, but I would like to have 0 to 64000. Thanks, Jack ...more >>

Hi there, I'm trying to connect to my AS400 server thru a LINKED SERVER, but = I'm having some troubles. If someone have this solution Running or has passed thru it before, = your help will be appreciated. Thank you very much. Below is the full script I'm Using to create ...more >>

DBCC ShrinkDatabase (database_name, target_percent) Lets assume one of the data files is 8MB with 6MB of data and 2MB of free space. run DBCC ShrinkDatabase (db_name, 25) What will be the file size and how is this calculated and can you provide another example. thanks...more >>

Hello, I'm new in database programming with Sql Server. I'm coming from ms access databases. Could someone tell me : 1/ What is the difference between a view and a stored procedure ? (can a stored procedure be a 'select' so returning rows like the views do ?) 2/ What is a trigger and wh...more >>

Q: how do u make subdatasheets in SQL when the data is being use in access ...more >>

Hi, Does anybody know how to save the Query Analyzer message in the application / client? Thanks Frank...more >>

How can I delete all the duplicate rows. For example: I have following data Identity Date Description Amount 1 23/02/2003 ABC 2000 2 23/02/2003 ABC 2000 3 23/02/2003 ABC 2000 4 2...more >>

I use ALTER TABLE MyTable ALTER COLUMN MyColum VARCHAR(50) collate Latin1_General_CS_AS to change a column's collate. I can use DATABASEPROPERTYEX() query the database collate but there is no collate property type in ColumnProperty() to query a column's collate. Is there any other way...more >>

create #table(A nvarchar(30), B nvarchar(20), c nvarchar(15), D int) insert into #table select A,b,c,d from x,y,z group by a,b,c,d select *,(select max(d) from #table t where t.a = d.a and t.b = d.b and t.c like 'm%') as mx1 drop #table go when there is such data as t.c like 'm%', i...more >>

create #table insert into #table select A,b,c,d from x,y,z group by a,b,c,d select *,(select max(d) from #table t where t.a = d.a and t.b = d.b and t.c like 'm%') as mx1 drop #table go when there is such data as t.c like 'm%', it works OK but otherwise, i get error: not supported, ...more >>

I passed a parameter - a user specified database name to a procedure, but when I try to create it using CREATE DATABASE @dbname, (@dbname is the parameter) I got a syntax error near @dbname, Any idea what went wrong? thanks. bead...more >>

Hello, I need to make a copy of a database. I mean to create database with another name that contains same data(stored procedures and tables) as source db. Is there any simple way to do it using SQL-DMO or T-SQL ? Thanks in advance. Michael Gesser. ...more >>

Hi all, In an ASP page I am inserting some data, on my development server it works fine with a normal update, on the live server (hosted by someone else) - they have different settings for the regional stuff and I have to use the CONVERT statement on dates to ensure they go in correctly. Th...more >>

I have a table (Table1) that has an ItemNbr as the primary key. I have a set of views that pulls apart a description field from the table and puts each piece into a new field in the view, basically Dim1, Dim2, Dim3, etc. Based on the ItemNbr and the number of Dims populated, not all will be), I ...more >>

I have written an SQL script to update some tables and produce an out to another table. I want to run this script as a stored procedure to run every day at 7:00 am Can any tell me how to do this and/or point me to a sight that gives me step by step instructions. Thanks Regards New...more >>

hi, When the sql server and sql agent are first started, are their status being written to any system tables or logs? I want to write a store procedure to record the server and agent start-up status. Many thanks. JJ...more >>

Why when I use the insert statement with 'password' the value 'password ' (with a space at the end) is inserted into the database. Honest this is happening and the password fails when it comes back in the User Interface Process Application Block. thanks ...more >>

How do null values affect b-tree index searching? For example, if a table has a date column and all rows with a null date are needed, is the fact that the value being searched for is null, any different than if it were a particular non-null value? In addition, do null values in a composite in...more >>

I need to generate a .txt file from a table and place in the x:\temp\ location. I know I can create a .txt file in DTS but how can I make that happen trigged by the event of the table been successfully loaded. I've been told I can use the FileSystemObject in DTS but I've never done befor...more >>

Hi I have address with below format in table and I need to select in one line. Is there any functions i need to use remove one keyboard enter. select * from #temp address ---------- 4620 S LAKE PARK AVE APT 1 W --I need below format. 4620 S LAKE PARK AVE APT 1 W Any help will b...more >>

I just moved a database from a windows 2000 server to one running Windows 2003. A query that was working fine is not working now. If I do a select count(*), date from table1 where date is null I get a very high count and a date that is not null, and not the same everytime I run the query. ...more >>

I am trying to create a trigger that creates child-rows automatically when a parent row is created. I have three tables with the following columns. Table: TableA Columns: aID (pk) (int) Table: TableB Columns: bID (pk) (int) Table: TableC Columns: aID (fk), bID (fk), v...more >>

I've been given a song and dance why we don't use them here but everything I read says they are best practice. I've created a few on our larger tables and most of the queries we run against those tables are now much quicker. Needless to say, the dude that doesn't like them is quiting and I...more >>

Hi all, I'm trying to copy some fields from one table and insert them into another. I've used variables in a select statement, but one of the source fields happens to be a TEXT datatype, and SQL is NOT happy at all. I've read somewhere about the command WriteText, but really can't see how t...more >>

So... I have the SQL Server 2000 installed in a machine with Windows 98 and I can't start the SQL Server Agent automaticaly when the OS starts, this option in Enterprise Manager is disabled. How can I start the SQL Server Agent when the OS starts??? Thank's ...more >>

I have a table set up as follows: Product TranType Amount 255 1 20 255 1 15 255 2 25 255 3 35 What is the best way to accomplish this? ...more >>

Hi all, I would like to know the optimal number of columns in a select statement when more than 2 tables are joined. Also I have a feeling that the width of the result set in bytes would also affect the performance of the query. I am looking into creating views in DB2 with big query statements in...more >>

Hi, I'm a long time mySQL/PHP user. For a new website I'm creating, I need to use MS SQL/ASP. I'm not quite sure where to start. I've looked through a few ASP books and the syntax seems simple enough, but I'm confused about MS SQL. I've seen a lot of screen shots in books of the MS SQL interface. Wh...more >>

I have been asked by one of my sys admins to remove the SQLDebugger login because he heard it was a security risk. I read a document from KnowledgeBase which indicated that only when you give explicit permissions can it cause a security leak. I also found that I can disable/enable the acco...more >>

I asked this question a few months ago, but my PC went belly up so I apologize... I have a table which shows events with begin times and end times. I want to create computed columns for each hour in the day, i.e. h00, h01 ... h23, and have those columns filled with a 1 if the event has any ...more >>

I have a batch process which runs every 15 minute, which loads several files into the database. Sometimes the files might not exist. When the file does not exist the entire batch fails. This causes the entire batch to fail. Basically, even if the file does not exist, it should fail gracefully ...more >>

Hello, From visual studio .net, is it possible to connect to an external sql server ? (out of the local network) If yes, - how to 'see' it in the server explorer. - how to make the connection with ado.net objects Regards, Cybertof. ...more >>

Hi I'm trying to pass params in SQL, but I can't use Stored Proc's since I want to pass a WHERE clause and that is so much easier using a VIEW.....However I know you can't pass params in VIEWs, is there a way I can get around this? -- Kind Regards Rikesh (W2K-SP3 / SQL2K) ...more >>

Suppose I have a table with a single integer column. The table consists of the following values: 1, 2, 3, 4, 6, 7, 9, 11, 13, 14, 15, 16 I want a result set like this (columns separated by a ,): 1,4 6,7 9,9 11,11 13,16 This result set shows me the ranges present in the original table....more >>

In my access queries I use the First function. Is there an equivalent function in SQL Transact SQL? I need to know how to convert to SQL Server. Access: Select First([field]) ... SQL Server Procedure Code: Select ??? Thanks, Peter...more >>

All Is it possible to increase the Column lenght in SQL Server 6.5 I have tried to use an Alter table command but but this does not seem to support the column modification. Only adding a column or adding / removing constraints? Is this a limitation for SQL 6.5 only or even evident in SQL...more >>

Is it possible to use log shipping with sql2000 standard edition? I was under the impression you could. But I am reading a book 'Microsoft SQL2000 survival Guide' and it states: "Log shipping is included only with the Enterprise and Developer editions of SQL server. If you are running the...more >>

Hi all, Will backup the tran-log automatically shrink the physical tran-log file size on the disk? Working on sql 2000. Many thanks. JJ...more >>

I want to do a query on 3 tables. In the example below if there is more then 1 record in Table2 linked to T1 then multiple rows are returned for each table 1. SELECT T1.MT_ID, T2.FULL_NAME FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.MT_ID = T2.MP_MT_ID AND T2.ISActive = 'T...more >>

Hello, I have a statement: SELECT @DAY1SUM COUNT(COLUMN1) If this is none I get a NULL and not a Zero. How can I set it up so if it is NULL send a Zero back and not a null. Thanks, Jack ...more >>

I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse: Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_...more >>

Hello, Does SQL Database support multi-site database? Thanks, Jim. ...more >>

hi is there a better way to do this? i am trying to insert the records of one table that are lesser than today into another table. but i feel there is no need for me to write a cursor. there should be a way to do this usinga sql query create procedure sp_updateBookingInfo as if exists (S...more >>

I'm doing basic inserts into a database, and it seems to randomly give the following error. Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be...more >>

Hi , is there any way to asign a value to a run time variable? decalre @tmp_var nvarchar(10) set = '@tmp_var' +'1' and want to set a value for set @tmp_var1 = 'ABC' set = '@tmp_var' +'2' set @tmp_var2 = 'DEF' Thanks, Pat ...more >>

Hi Freinds: I have normalized tables: INVOICE nid int namout money and INVOICENOTES nnoteid int nid int cnote varchar(100) NOTETYPE nnoteid int cnotetype varchar(10) I need to denormalize the invoice and invoicenote tables like: INVOICE# NOTE1 NOTE2 NOTE3 or IN...more >>

Can the SELECT ROWCAOUNT statement be used within a UDF? I kept getting an error. My function returns a table. ...more >>

Could someone please tell me how to retrieve the description of a backup after backing up a database using the SQL-DMO Backup object and its BackupSetDescription property. Thanks, Mark ...more >>

The question is this How Many Union Can I make in a query with Sql Server 2000 ? Example: SELECT A,B From Table 1..... UNION SELECT A,B From Table 2..... UNION SELECT A,B From Table 3..... UNION SELECT A,B From Table N..... How Many Table and How many unions ? Thanks...more >>

I have 3 tables I want to join sales, sales detail and payment. I am trying to fill a data grid with the sales info and then I want to have a column on the end with amount owed. so I want to add/subtract the SUM(tempSalesDetail.SalesPrice) - SUM(Payment.paymentAmount) + tempSales.totalShipping +...more >>

I have a procedure in server A, inside procedure, the codes are like following, do you think it'll rollback the linkedserver records if encounter the errors? ............. BEGIN Distributed Transaction Insert into user.dbo.employees values (@id, @name,@address) if @@ERROR<>0 goto ...more >>

Hi Everyone, Is there an easy way to update just the Year part of a date field? Here's what I have: RetentionID | RetentionCalcDate | RetentionReviewDate ====================================== 1 | 1/1/2003 | 1/1/2007 1 | 12/31/2004 ...more >>

Anyone know of any good web sources on creating sql statements?...more >>

Hi, I have a query which I would like to turn into a view. The query is:- SELECT extracurr_groups.SetId, lecturer.lecturerId, GroupId, extracurr_= groups.Name FROM extracurr_groups CROSS JOIN CMIS_Local.dbo.LECTURER lecturer WHERE extracurr_groups.SetI...more >>

Hi all, I wonder if anyone else has the same error message like below. I am try to go to the next page and I got the error below. Ang suggestions. Thanks. Error message: Newsgroup not available...more >>

Im connecting to this group using Outlook Express. (In my companies research room.) I see very current, up to date posts. However, when I log on to the MS site and connect to these groups (from my desktop) the posts are over 6 hours old. Could you guys please update? ...more >>

Apologies if this is covered elsewhere on this newsgroup (I couldn't see = it) or if this is the wrong NG. If I try to access this NG using the WWW interface at support.microsoft.com= , I get "Newsgroup Not Available". Any ideas, anyone? TIA Paul...more >>

Hi, I am using UPDATE FROM statement to update 2 fields in 1 record (table named Queue). There are over 30 connections (applications written in C#) that concurrently execute the same stored procedure with UPDATE FROM statement. But only 1 connection must be able to update that record wh...more >>

Is there any way to use the Top N statement dynamically... Declare @Top integer Select @Top=8 Select Top @Top etc......more >>

I use multiple databases on a proyect I'm working on. I need to know the DB i'm accecsing so I don't have to send it as a parameter everytime I use the SP, plus I shouldn't hardcode the DB name on the SP. If any1 knows how to get the DB name inside a SP, I would apreciate your help -- F...more >>

I want to run the following code but using domain authentication via a passed in id and pass. Is this possible? If so how? Joshua Tipton select * from OPENROWSET('SQLoledb','UID=domain/uid;PWD=UPASS; Network=DBMSSOCN;Address=IP_NUM,1433;TIMEOUT=2000', 'select * from INFORMATION_SCHEMA.tables wh...more >>

Good day all. I need help writing a sql statement. The table name is RCI. I want to pull data where field C/O = O and field When = greater then 30 days. I would like it todisplay all the information on the records where these above information is true....more >>

I'm attempting to to use ADO.Net to execute a RESTORE DATABASE. Here is a snippet of the code I am using: SqlConnection connection = new SqlConnection(); string connectionString = ConfigurationSettings.AppSettings["ConnectionString"]; SqlCommand count = new SqlCommand(); SqlDataAdapter da =...more >>

Hello People, I want to create a stored procedure which goes through a table row by row and looks at a field, which if its not in another table it will add it to the other table, or if it is it will update it. SQL Cursors seems the only way to step by step go through a table looking at the ...more >>

What I am trying to do is that if a record is updated in the CLSSTATUS table and the MODIFYDATE field has been updated and there is a record in the CLSRECORD_ACCESSED table that has a CLSRECORD_ACCESSEDID field that matches the CONTACTID field of the updated record, I want to insert anothe...more >>

Hi All, I have a new table which contain a field with image datatype. How do I store my images in this new table. I tried googling but of not much use. Can anyone explain a step by step solution for this. My table is like this: CREATE TABLE [dbo].[TblTest] ( [ID] [int] IDENTITY (1,...more >>

Dear Friends i am witing the following query. Select(CASE(sls_GROSSWEIGHT) WHEN '' THEN 1 ELSE SLP_GROSSWEIGHT - SLS_GROSSWEIGHT END ) AS TEST FROM dbo.STOCKLISTPUR Where i want that if the SLS_Grossweight is Null it should show the value as 1. I am not able to do the same ...more >>

Hi there! I've got a problem using ADO to execute a stored procedure (sp) on sql server, which gets two dates and fills a table with some data on that interval. Typical execution time is about a minute for a 30days interval. Since the sp takes potentialy a long time to execute, I execut...more >>

...more >>

Hi I got this query it works great. : declare @cyname as varchar(100), @key as int set @cyname = ? set @key = ? set @key =(select top 1 user_key from users where (fk_record_type = 101) and (datediff(mi, last_update, getdate()) > 30)) begin transaction if(@key is null) begin insert into...more >>

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

MS Customer 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 maintain t...more >>

I have a remote host (HP-UX 11.0) mapped to SQL Server 2000 (PC, Windows 2000) as drive Z:. However, I am unable to access it using T-SQL statement. While executing the following T-SQL statements, exec master..xp_cmdshell "echo abcdefg > z:\abc.txt" However, I am always encountered wit...more >>

Microsoft Partner 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 help...more >>

Has anyone come across a change tracking tool for SQL Server. Specifically the scenario I want is the following : A Production DB needs some modifications to its content. This tool will copy the DB to a dev environment. The Dev environment will be 2 copies of the DB, 1 as a control set and the...more >>

Is there any way to get the domain users in TSQL (SQLServer2000) ? Thanks in advance, Nuno ...more >>

should i use the SYSSERVERS system table? 3X....more >>

Hello, I'm trying to figure out a way to automatically receive (via email) the results of DBCC CHECKDB, but only when errors are found. Currently, the best I can do is get a report every time it runs, even when no errors are found. Any ideas? Thank you, Garrett...more >>