All, The usual SMTP port is closed for the security purpose on our server and I can't use a standard C# way to send my Email messages. How can we send an email message using MS SQL Server from C# code? Any examples, links? Thanks, Just D. ...more >>

Hi, How would I create a table programmatically in a database. I am using C# 2005 Express edition. For example I want to create a table named tblHours in a database dbEmployees with the columns hours worked (integer), hourly pay (currency), etc.... TIA Roy ...more >>

Using DBCC Showcontig is useful for working out the level of logical and extent scan fragmentation for tables which have a clustered index. However for heaps (tables with no cix) is there a definative method of working out whether fragmentation exists? Regards, Andrew ...more >>

Hi All, I have a routing query running in QA windows. I use a "waiting for" and "while 1 " statement to force it run every 15 minutes. Dead lock could stop it even the channce is very rare. What's the best way to create a watch dog style routing script to watch it and restart it incase fail...more >>

Hi, Is there a way to clear the SQL Server Event Log without stopping/starting the SQL service? I am running SQL Server 2000. I am not talking about the transaction logs. Thank You. Dan ...more >>

I have a database table with a field of datatype "money". I need to have this populated with rows that only have data up to a maximum of two decimal places (i.e. pounds and pence). However, I have come across some rows that have up to 3 significant figures after the decimal points (i.e. 1/...more >>

I've read through a number of similar questions in the forum, but because I'm so new to SQL Language, I didn't see one that related directly to my attempt at making mine work. I'm querying an SMS 2003 database that has self-built entries, etc... by SMS. I'm looking to query this db and repor...more >>

Hello Everyone, I am trying to calculate the average of a column with integer data type. The table has millions of rows and I get overflow error message when I run the following statement: SELECT avg(col_name) FROM tbl_name Not sure how to use CAST or CONVERT function with Average functi...more >>

I have inherited an old timecard web app built with classic ASP and SQL = Server 2000. I need to build a weekly timecard input/edit screen that = returns 7 columns (for each day of the current week) plus 1 column for = the project name. The number of table rows is determined by the number = of p...more >>

I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s) affected" when I execute the sproc. It seems to happen sporadically too. Sometimes if I exit QA and go back in, it will execute w/o returning row counts - other times I continue to get the number of rows affected returned...more >>

Hi I am using a stored procedure in SQL2000 for retrieving file maximum weighting from a db. The table consist weighting (int) and variable id (Unique identifier) columns. My intention is to get the maximum weighting of a variable among the passed variable ids. So I am generating a ...more >>

Hello, how come the SQL Server standard edition is so expensive? If i develop a .Net web application and want to host that on a server, with an undertermined number of users, i need to purchase the Processor version, but that is so expensive... ? Is there any other way to have SQL Server 2...more >>

Hi is it possible to sort data ASC or DESC using a CASE Statement based on user input parameter ? Thanks ...more >>

Good People, I want to compare one record to the next, that is, say I have one table call it T1 with recordId (int) and code (char (5)) recordid is identity field. I want to compare 'code' of the first record on this table with 'code' of the second record on this table, the second record wit...more >>

Hi, I am trying to create a temp table and bulk insert data into that temp table. I am using the 'bulkinsert' login is a part of a server role bulkadmin and has read access to databaseA. Create table #My_Table ( Col1 varchar(14), Col2 varchar(60)) then trying to bulk insert using ...more >>

How to drop all primary key of all tables in DB? After reading a document for explaning how to use clustered index, I knew set all primary key to be clustered is not correct for me. I would like to drop them. recreate a new nonclustered primary key and create other clustered indexes. N...more >>

how to get compatibility level of a database? --UsingSQL2005Dev ...more >>

Don't know if this is the proper newsgroup to post to. Sorry if not. Can't figure this one out. Using Access 2003. I have 2 tables, IMast (table of part info) and THist (part transaction history). I need to select all parts that have a product code = "CAST" and list the last 10 history tr...more >>

Hi All, I had a system failure and I noticed a particular spidxx throwing constant warnings in the SQL Logs of "Not a Buf latch" Since the server was not accessible I had to hard reboot the system. I was wondering how to trace what command was being executed by the spidxx..which actually caused ...more >>

Hello - I "installed" KB884016-V2-x86.exe. I have "installed" in quotes, because it ran though almost too quickly, informing me it installed successfully. I then tried installing .Net Framework Version 2 Redistributable Package x86 (dotnetfx.exe). Its strange interface had only the opt...more >>

How can I filter a query using a cocatenated field? For instance, I have a query (for instance): Select id, ClientCity + ' ' + ClientState + ' ' + ClientZip as ClientInfo from tblClients I want to be able to say "where ClientInfo like '% ' + @Parameter + '%'" so that I can qu...more >>

Hi all, I have sample table with the next format. 3 ID int 4 0 0 ParentID int 4 0 0 Title varchar 50 0 0 RecordDate datetime 8 0 * ID is automatic Record ID. * ParentID is (if set) the ID in the same table which the record is related to (if not will be 0). * Title is free text. * Recor...more >>

Pardon my newbieness: I have a column that could contain numbers or text (not my idea). All of the values are returned as text. I want to return the value as a number if it is a number, otherwise (of course) leave it as text. What can I do? ...more >>

Hello, I'm having problems with this piece of Transact SQL. What I need to do is to get a date 2 months ahead of an entered date. However, I am not supposed to use this: @dt+60 (in order to get 60 days exactly). Consequently, if I've entered '12/21/05' as the original date, I need to get the 2...more >>

I have a large database consisting of almost 7 million records, each comprised of 30 fields! I execute approximatelly 500 queries on this database daily. Each query poses conditions on the 20 (out of the 30) fields. Thus, most tables I get are null, but some do contain data, after all. The ...more >>

Is there any way I can put the following consolidated statement into a proc or function for reuse?? I have to use dynamic exec statement because filename,extension, or linked server could change <consolidated> create table #t1( c1 int, c2 int, c3 int, c4 int, c5 int ) inser...more >>

We're having another one of those ridiculous meaningless arguments. Consider a stored procedure for doing an insert to a table, where we want it to have "indempotency". Below, col2 and col3 represent the natural key. Col1 is the Primary Key and an identity that gets returned to the applicat...more >>

Can SQL Server 2000's Select statement assign multi-variables from Table? e.g. Select @CustomerName = CustomerName, @EffectiveDateFrom = EffectiveDateFrom, @EffectiveDateEnd = EffecitveDateEnd from Customer where customercode = 'XXX'. ...more >>

Hi All, I need add one column in one table, but this table already have rows, and this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this column with values? Have any way to do this? ----------------------------------- SQL Server 2005 -------------------------------...more >>

I create a UDF based on a table. The table (say tblA) has an index on column A. When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB where tblB.colA = MyProc.A", will it use the index on tblA column A , or do I need to (and can I) create an index on the UDF ? CREATE func...more >>

Hi all... I am having a problem with what I thought should have been quite simple: I have a table: declare @tb_Results table (PeriodSID int ,PeriodStartDate datetime ,PeriodEndDate dateTime ,TotalDaysOff real) I want to find any records that have the three fields PeriodSID ,PeriodSta...more >>

I have a stored procedure that takes 6 seconds to run. When I put the exact select statement from the stored procedure into a function, it takes 15 seconds for the function to run. Why is the same select statement takes a lot longer in a function than in a stored procedure ? Is there a way to...more >>

Hi I need to remove leading zeros from a text string of numbers. For example:- '000012' needs to be '12' '000123' needs to be '123' '012345' needs to be '12345' '008000' needs to be '8000' '01203' needs to be '1203' There can be between 1 and 4 zeros at the front of the string. Removi...more >>

I apologize if anyone has already answered this question. I was away on vacation and forgot to check this newsgroup, and now I find I'm missing all posts between 12/21/05 and 12/31/05 and I'm not sure how to get Outlook Express to get them. So, if you did post an answer already, would you plea...more >>

Hi, is there a similar feature in the 1.1 Framework available? Any best practice for creating an updated cache on the client? Thanks in advance for any hint Anthony -- Anthony Malt ...more >>

Hi, i have two tables, each has a columnn called "value". Now i want all "values" from table_1 which does not exist in table_2. I tried: select table_1.value from table_1 where not exists (select * from table_2 where table_2.value = table_1.value) Is this the fastest way (in SQL-Server2000)? ...more >>

Is there a query that i can run to find reserved words in the tables present in my database? Thanks!...more >>

I've got a user defined function that returns a table variable. A call to the function returns 25 records. If I copy and paste the script that comprises that function, and run just the script, I get only 8 records. The function call is simply: SELECT * FROM fn_x(91,null,null) The script i...more >>

What is the SQL equivalent to a REPLACE ALL command in VFP? I found this in the help section: SELECT REPLACE('abcdefghicde','cde','xxx') GO Is this the full syntax and where would I use this command, I tried in the EM but it doesn't seem to work. ...more >>

I have a table with a column varchar(50), say colA. How can I create a sql query that returns all records with duplicate colA ? For example: colA colB 1 A 2 B 2 B 3 C 2 is the duplicate records for colA. How can I return those records ? Thanks. ...more >>

Hi, I am adding a row to a table in pubs database from an asp page. However, the value I have chosen for rows does not allow me to add record with the following errors: Microsoft OLE DB Provider for SQL Server (0x80040E2F) INSERT statement conflicted with COLUMN CHECK constraint 'CK_emp_id'. ...more >>

I'm sure that I'm not the first to complain about this, but it bothers me that it seems that MS is now forcing us to use this new bloated SQL management studio. Don't get me wrong, it does a lot and is a great management tool. However, nearly everything I do with SQL Server I do through script. ...more >>

Hi all and Happy New Year, I have a data table that contains addresses and I have created an INSTEAD OF INSERT, UPDATE trigger that does the following: Checks for Duplicates - this works fine Checks for Modifications - this does not work I am using a column called RowVersion that is of t...more >>

so i have two tables that looks something like so: CREATE TABLE transactions ( transactionumber INT IDENTITY (1, 1) PRIMARY KEY NOT NULL, transactionamount MONEY, transactiondate DATETIME ) go CREATE TABLE credits ( creditnumber INT IDENTITY (1, 1) PRIMARY KEY NOT NULL, transaction...more >>

Hi, I am using VS 2005 and SQL server 2005. I want to create a trigger at runtime. The trigger has to be created when one of the rows in user-specified table is deleted. User will specify the name of the table at runtime. Hence I cant create trigger at development time. Can I create such a tr...more >>

Hi, Not sure if this is the best newsgroup to ask this so let me know if there is a better one. I am in the process of investigating converting a component which uses ADO recordsets to return data from a DB, to making use of the FOR XML statement. When using recordsets the default date fo...more >>

Looking for an overview book on SQL Server 2000, can anyone recommend a Title or Good author? Id like to learn the basics and how to use query analyser to manager the database setup. Also the book can show what are the SQL functions (such as date manipulation) and how to use them. Thanks...more >>

Hi All, I'm trying to uninstall previous version of .Net Framework before doing a clean install of SQL Server 2005. When I tried to uninstall in Add/Remove, the following file it asked for, and I don't think it is on my harddrive - tmp1DD.tmp - any help that I could manually remove .Net Fr...more >>