Hi there, Need to know how can we write single query for the example below; Table1 Name: Users UserID + UserName ---------------------------------------------- u1 | User - 1 u2 | User - 2 Table2 Name: Program ProgramID + Program Name + Teacher1 + Teacher2 ----------...more >>

A client has asked us to develop a web appplication using SQL Server 2k as the back-end DB. The client's project consultant wants us to use the schemas in the The Data Model Resource Book by Len Silverston (ISBN: 0471380237) Amazon link: http://www.amazon.com/exec/obidos/tg/detail/-/0471380237/...more >>

Table -- Survey_invites Primary key Clustered index on (survey_id,email_id). Query 1 select * from survey_invites where survey_id='003' -- by default Index not used ( need to give hint to make use of index) with hint it takes 1 sec v/s 3 min without hint !!! Query 2 select * from survey_invi...more >>

How can I run sql server 2000 system stored procedures in Vb-6. Say, in query analyzer, I want to grant server role member 'syadmin' to the user 'luqman' sp_addsrvrolemember 'luqman','sysadmin' I tried to do so in Vb-6 as under: Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection...more >>

Hi, I need to create a temporary table based on the value of another table. for example i have a question table ID QuestionType 1 money 2 date 3 bit and I need to create a new table Repsonse RID Question1 Question2 Question3 this table '...more >>

trying to copy images from an employeeimage table to the employee table itself this command does not work update employee set picture = ( select picture from employeeimage where employeeimage.employee = employee.pk ) error says 'Server: Msg 279, Level 16, State 3, Line 1 The text, ...more >>

Is there a procedure that can tell you the amount of free space left in the current database? Enterprise manager always seems to know this, so how does it find it out? I looked at xp_fixeddrives but that only tells you the amount of free space on the whole drive. I'm using MSDE with its 2G...more >>

When I divide 100 by 3, I want to get 33.33 Here is what I found: select 100/3 returns 33 select 100/3.0 returns 33.333333 select round(100/3, 2) returns 33.330000 How to get that clean 33.33? Chen...more >>

How can I get the non-zero min ? For example if I have 2,0,13,6,45,10 normal MIN would produce 0 but I would like to get 2 Zeno ...more >>

I've got a UDF that executes the following query: SELECT SUM(dbo.AdjustedOrderTotal(tblOrders.ID)) FROM tblOrders INNER JOIN tblPeople ON tblOrders.SoldToContactID = tblPeople.ID WHERE tblPeople.CompanyID = 1 The problem is this query takes about 12 seconds to execute. When I remove the ...more >>

Hi, How do I return string with spaces and no spaces using the same search criteria. For example I have the following table. Create table #org (RowID INT Identity(1,1), OrgName VARCHAR(40) ) INSERT INTO #org Values('GC Systems') INSERT INTO #org Val...more >>

Hello, when I insert a value like insert into test_table(date1) values ( (convert(datetime,convert(varchar,getdate(),108))) ) my table result is '1900/1/1 17:01:25' What do I have to set to become only the time like 17:01:25 Thanks for any help in advance kcobain ...more >>

Hi, Do i need to populate the Full-Text catalog and Full-Text Index both in order to get the most updated data??? I thougth all I need to do is to populate the Full-Text Index, if so, what is the reason to populate the catalog, too???? If I need to populate both, do i have to populate the cat...more >>

Hi Freinds, I am using the following script to restore a database as a new database : USE master GO RESTORE FILELISTONLY FROM zzz RESTORE DATABASE corp_interface FROM zzz WITH RECOVERY, MOVE 'corp_data' TO 'g:\corp_interface_data.mdf', MOVE 'corp_log' to 'g:\corp_interface...more >>

This question is not answered. The previose heading of this question "Index confusion" was simillar to another question published in the newsgroup. I appreciate anyone can drop a idea on this. //////// I have a temp table and created two indexes on it. Please See below ----------------...more >>

I'm using sp_getapplock (session type) for some application level locking (mainly to prevent worker units from processing the same items in work queues or single-use type access to items outside the scope of the database) and was curious is if others are using this and what alternatives there mi...more >>

I'm pulling in data from multiple sources at 30 second intervals, each of course in a different time zone. The systems are able to give me a UTC, so it made sense to store all dates that way. When pulling large amounts of data, this function appears to be a bottleneck. I'm wondering if I sh...more >>

I need a trigger to update the updated records, something like this: CREATE TRIGGER [Updated] ON [dbo].[MyTable] FOR UPDATE AS UPDATE MyTable SET MyTable.Modified=GETDATE() WHERE MyTable.SculpID=updated.SculpID; I get an Error 107: The column prefix 'updated' does not match with a table ...more >>

What would be the best way to do a nightly "image" backup from serverA to serverB. ServerB is our development server and I'd like to have a copy of the database on ServerB refreshed from ServerA each night. ServerA's database is like 700MB so it would take some time or is there a solution to...more >>

I have a temp table and created two indexes on it. Please See below ---------------------------------- CREATE TABLE #Invoice ( InvoiceID int NOT NULL, CustomerKey varchar(20), DebtorId int, Reference varchar(50), BalanceDue money, DebtID int, Reason varchar(100), DebtStatus int, ...more >>

In Northwind, the following query shows customers who don't have any orders: select ContactName from Customers where customerID not in (select customerid from orders) ----------------- Maria Bertrand Diego Roel How could I write a query to return 1 or 0 if they have orders or not: ...more >>

Hi, I create a SELECT statement dynamically and need to get its result by a variable: declare @vcTableName nvarchar(50) declare @vcFieldName nvarchar(50) declare @Sql nvarchar(4000) declare @ret_value bigint set @vcFieldName=N'orderid' set @vcTableName=N'orders' set @Sql=N'select @ret...more >>

I wanted to find some parameters for the job object and cant seem to find the help on BOL ...more >>

SELECT a, b, c, d FROM mytable produces 23,301 rows. SELECT a, b, c, d FROM mytable GROUP BY a, b, c, d returned 23,300 rows, one fewer than above. Why the discrepancy? A duplicate somewhere?...more >>

Can someone give me an example of SQL to import from one server to another. I'd like something like import from ServerA to ServerB all the rows of a table called Customers where CustID=12345 to a table (append) on serverB called Customers. Thanks for any help! Jon ...more >>

Is there a way to use a column alias in an another calculation within the same query? Since I am using some long and complex logic to compute total1 and total2, I don't want to repeat the same logic to compute the ratio of those two columns. I know that I can do a nested query, but that seems too...more >>

Hi! I have two tables I want to union, tableA and tableB. Apart from a single column 'common', the tables have different column names. I want to select all columns from both tables, and I want columns from tableA to get NULL values for tableB and vice versa. Example: tableA contents (...more >>

I need to be able to have Containers "in" Containers. I have thought of the adjacency model and the nested set model but neither seem to fit right. Nested set seems to work when there is ultimately one root but in this case there can be many roots. This is a warehouse application and a ...more >>

What does it mean when there are 2 froms in a SQL statement, Such as delete from TABLE1 from TABLE2 where TABLE1.COLUMNNAME = TABLE2.COLUMNNAME My best guess would be that SQL will delete all items in table 1 that have a matching item in COLUMNNAME from table 2 Am I close? Or way off? ...more >>

Has anyone found documentation on how to add custom rules to the SQL Server Best Practices Analyzer. Assembly names are in the sqlbpa repository leading me to believe that other rule assemblies can be added. The table is [bpa_managed_types] TIA MikeL...more >>

is this an expected behaviour of aggregate functions? USE Northwind DECLARE @ID INT SET @ID = 0 SELECT @ID -- 0 SELECT @ID = cat.CategoryID FROM dbo.Categories cat WHERE 0=1 SELECT @ID -- 0 SELECT @ID = MAX(cat.CategoryID) ...more >>

I want to run the index wizard on a production database but I am afraid of it hurting performance. I have a backup of the database from last week that I can load on a local server and run the index wizard on it. Will I get the same result as if it had been run against an up-to-date database...more >>

Okay.. I've looked all over the newsgroups and forums and everyplace I have ever found an answer to any question I have ever had. No luck... ARG!! Here is the low down. I have two SQL2k Servers located on seperate networks, connected via a VPN connections and accessible via internal IP's...more >>

Okay.. I've looked all over the newsgroups and forums and everyplace I have ever found an answer to any question I have ever had. No luck... ARG!! Here is the low down. I have two SQL2k Servers located on seperate networks, connected via a VPN connections and accessible via internal IP's...more >>

I have table SELL with columns: productID(varchar(20)),quantity(int),type(tinyInt),sellDate(datetime) I would like to get sum of sell for one day, devided by quarter of hour for all products and for only products where type=1. The result should be like his: time sum(...more >>

Hi all, We have an Application (MS Navision as it happens) which seems particularly paranoid when performing record updates (and reads) when it come to locks. Basically there are a handfull of update intensive tasks which take so long that multiple users cannot update at anything like the same...more >>

I am using the Ole DB Consumer templates and wondering, how do I get the additional result sets returned by sp_help MyView (assuming MyView is a valid view.) I'm currently using the very simple: CCommand< CDynamicStringAccessor, CRowset, CMultipleResults > l_oCommand; I can get the initia...more >>

I'm trying to determine the best approach for rounding in an application I'm building. Unfortunately it appears as though SQL Server and VB.NET round in different ways. SQL Server select round(123.465,2) returns 123.470 Which I think is correct. VB.NET Math.Round(123.465, 2...more >>

Can you develop new applications with a SQL Server beta release or should you wait for the commercial release? For example, could you build a new application with SQL Server 2005 Beta 2 and release it in production and then upgrade the database to the commercial version when it releases in the...more >>

Has anyone ever put a foreign key constraint between 2 columns on the same table? What would be the pros and cons of doing this? ...more >>

This code produces a table scan even though the referenced column is indexed. Is there a way to rewrite a query like this to avoid a scan? select * from TsysQueue where (TSysQueue.RequestDate >= DATEADD(d, -3, dateadd(mi,-10,getdate()))) -- SQL2K SP3 TIA, ChrisR ...more >>

Hi, Is there a way in T-SQL to update several fields of a table with fields from another table using a select statement based on a key? I remember doing that in Oracle. If so, can you please give me the syntax. Thanks. ...more >>

I am trying to build a T-SQL procedure to restore a database that was backed up on one server using BACKUP, to another server using RESTORE with following sql statements: USE MASTER SET QUOTED_IDENTIFIER ON ALTER DATABASE Juris5591000 SET SINGLE_USER RESTORE DATABASE Juris5591000 FROM DISK ...more >>

I have an image datatype in the table that stores pdf document. I need to find out the size of this column and compare it with the actual file size in the NTFS. Any suggestions? -Thanks in advance....more >>

Hi, I am trying to play with the Full Text with Northwind database the table is Categories and the following command was executed: select * from categories where contains([description], 'drinks') Value in the field of Description -- Soft drinks, coffees, teas, beers, and ales it shows o...more >>

Hi, I am importing data from an Excel sheet into SQL. The data is quite simple: Excel: Name, Surname SQL: ID, Name, Surname ID is the primary key (type INT and Identity=Yes) I am using DTS to import this data. I have mapped the columns and all is OK. However, when i execute it I ge...more >>

Hello, Is there a tool someone has developed free or not free that can scan a database and look for custom rules for example: views end with _V default constraints begin with DF_ table names are not longer than 26 characters user defined functions are prefixed with UDF_ and so on and so for...more >>

Hi Say I've got a table with 100 cols 1 x PK col int Other all varchar. 100 mil records. NO indexes Now I want to do queries on this data, I can't query the table directly because if I do so it will be extremely slow without indexes, let's say I do create indexes, there could be more ...more >>

I've made database with a lot of forms and modules in MS ACCESS 2000. Now I want to transfer this database to MS SQL Server. It'll be OK with the tables, and may be the queries. But what about all the other stuff - modules, forms and so on? As far as I know SQL Server doesn't support such kind ...more >>

I would like to pass the name of a new table as a paramater to an sp. However CREATE TABLE wont accept a @paramater as the @TableName. How can this be done? Ta in advance, Michae McD...more >>

I Have 2 tables. I am trying to update one table based on the period value in recieved from another tableOne a fact table containg a period date and a value for all differnet markets and products. the period columns are datetime columns and are always the first of each month. the period table...more >>

Hello I am trying to create a table that will store filenames. I need a stored procedure that will be able to insert a filename, but must return the filenameid of the filename inserted. I have created the table like this: create table filename ( filenameid int not null constraint pk_filena...more >>

Hi, I have the following trigger which splits a spanning record from say 12/29/2004 to 01/07/2005, however, it creates 2 2005 records and I can't see why? --add new record to fit in next period insert into tblAbsence (strLogonName, intYear, strAbsRsnCode, dtmAbsStart, dtmAbsEnd,...more >>

Hello All I have the following scenario: Material Month StockMovement ======== ===== ============= ABC001 001 120 ABC001 002 ABC001 003 ABC001 004 158 ABC001 005 ABC00...more >>

How do I write a query to Select * from Data where age (is closest) to 48? I want the query to return 3.htm because the age is closest to 46. I can't use like or =>. I looked at using Floor and Ceiling. Any easy way to do this? Data Link age 1.htm 44 2.htm 51 3.htm 46...more >>

Hi, I have a stored procedure with some text parameters. These parameters are used to deliver xml texts to the sp for processing. I wanted to call this sp from another one but failed. Sql server doesn't allow to declare text type local variables, but without this how can one call an sp w...more >>

I am using SQL Server 2000. Let's say I have three tables as follows: Bids ---- BidID BidderUserID OpeningID BidDesc Openings ---------- OpeningID PaymentMethod AdminUserID Users ----- UserID FirstName LastName Now, suppose I want to run a query that brings back: Bids.Bid...more >>

Hi All, Below is a small UPDATE trigger written on tblTest. The intention of the trigger is to print tst field value whenever an update happens. But the problem is that when i write an update statement which affects more than 1 row, then only the last field value of tst is printed. ...more >>

hello, I'm using asynchron queries (ADO 2.8, MSSQL2000,VB6,SP6), there is button to stop the query (by .cancel method), but it hangs up... why? why have then cancel possibility, if users find better to call task manager....?????? Zoltan ...more >>