hi I am trying to convers an acccess sql given below to a view in sql server 2000 SELECT DISTINCT tblA.ID, Date()-(SELECT max(Date) FROM tblB WHERE tblA.ID = tblB.ID ) AS Expr1 FROM tblA INNER JOIN tblB ON ID; I TRIED SELECT DISTINCT tblA.ID, DATEDIFF(day, SELECT max(Date) FROM B , ...more >>

I have a database with lots of Stored Procedures and I want to encrypt all them . I need to know if it's an automatically metod to encrypt all of them. Thanks Gabriel . ...more >>

Hi, I have two physical disks that each have and NDF file(two different filegroups: FG1 on Disk1, FG2 on disk2). Also I have two tables that my queries always join them. Which decision will lead to better performance: 1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes on ...more >>

Apologies if this is the wrong newsgroup to ask this in. I couldn't find a 'newbie/general' forum for MS SQL. I'm fairly new to using MS SQL and ASP.net. I've done one site working with some other developers. I'm now on to my second site where I'm doing it alone. To start with, I'd like ...more >>

Hi All, I am having a hard time understanding when does SQL Server update metadata in sysindexes. I investigated a given schema and found out that data types were abused and I want to change it. After I execute "ALTER TABLE ALTER COLUMN" to change the datatypes, DBCC SHOWCONTIG (and other ...more >>

The query below is taking 3 to 4 seconds to execute. If I don’t use the variable ‘@iConnoteCodeFound ‘ then the same query returns the result immediately. There is a non-clustered index on sConnoteCode in the Connote table, but the query plan shows a search on the whole argument i...more >>

Hi all, I need to send SMTP mails from the SQL server everyday. I understand that I have to download the XPSMTP DLL and run a stored procedure everyday using the SQL Server Agent. This works fine; however, I have one problem. I need to send the result of a query as the message content in th...more >>

Gentlemen are trying an application that worked with access for MSDE to migrate and everything went well until that went to try to increase a registration in a table. using the following code: Dim MiRS As ADODB.Recordset Set MiRS = New ADODB.Recordset MiRS.CursorType = adOpenKeyset MiRS.Lock...more >>

I have a database that users enter in their the job name's and locations of projects they are working on. THey enter in revenue and expenses on the job until finished. Thus I created 2 tables. jobs jobID int jobEmpID int jobName 200 jobLocation char 200 startDate smalldate finishDat...more >>

Hi, We are designing a large-scale database application. The current one will receive information from many inputs constantly. We expect it to grow in one year to 60GB (on average of 4-5GB/Mo). The database then (after one year) will have a cleaning procedure that will delete informatio...more >>

Hi, Sometimes when I try to modify data in enterprise manager, I get this error: Key column information is insufficient or incorrect. Too many rows were affected by How can I solve this problem? Many thanks in advance. Leila ...more >>

My Powerdesigner helps me design a SQL Server 7 database (approx. 100 tables) and automaticaly creates primary key index as well as indexes for all foreign keys in each table. So I got a lot of indexes table and I am asking myself whether this is correct from performance point of view? Would...more >>

Hi, I have a Code Table which is: ID Address1 Address2 City State I need to create a view to pull three records from this based on three unique ID's that I have. Pseudo: Get Address info where ID=1 AND also Get Address info where ID=2 AND also Get Address info where ID=3 And do...more >>

In Access, we can do "IIf" statement. Can we do "IIf" in SQL Server 7 ? Thanks....more >>

Hi, I have implemented TF/IDF functionality with tokenizing, stop words, ranking features within SQL stored procedures. I have anoutput that looks like the following examples: token idf cross 1.79175946922806 duplicates 1.79175946922806 extract 1.79175946922806 messages 1.7917594...more >>

I bought a component to do calculations based on Zip code distances, and I'm trying to create a stored procedure out of their sample code to return the distance value. Here's what I have: CREATE PROCEDURE zipcode_distance @ZipStart nchar(5), @ZipEnd nchar(5) as DECLARE @Lat1 REAL DE...more >>

Is it possible to create a relationship to a table in another database, either on the same server or a linked server? - Dave ...more >>

I have 2 sql servers on my development LAN, both with same sa account passwords. I'm trying to "register" or "link" the 2 so i can run SPROCs from either against the other. I've gone exactly by BOL's example using sp_addlinkedserver, but I get errors. On server 1, when I run FIGURE 1, the 1...more >>

Hi, Can I encrypt a stored procedure in SQL2000 and if so how. Cheers...more >>

hi...this is no doubt easy but I cant do it below is a sproc i am calling from an ASP page ------------------------------ CREATE PROCEDURE dbo.populate_MAIN_visitors @IP nvarchar(20), @passScreen int AS declare @ident int declare @guid nvarchar(45) SET NOCOUNT ON insert ...more >>

sp_tables @table_owner = [dbo], @table_type = ['TABLE'] returns the below data WebPortal dbo BusinessDirectory TABLE WebPortal dbo dtproperties TABLE WebPortal dbo EntryTypes TABLE WebPortal dbo LeftMenu TABLE WebPortal dbo Movies TABLE WebPortal dbo Sample_ChangeLog TABLE WebPor...more >>

Hi, I am trying to create a table that has a primary key field SystemID The SystemID should start with an alphabet S and then should be a five digit number greater than 20000. It should only be 6 characters long as well. so valid systemIDs are S20001 S20005 S99999 I tried doing this ...more >>