statement 1: EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%') statement 2: EXISTS (SELECT City FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%') Do both statements have the same best performance? Or Statemen...more >>

Is there any way to convert a Hex passed as varchar Below works select cast(0xAD as int) But not this declare @name varchar(10) select @name= '0xAD' select cast(@name as int) --Gives a conversion error. Thanks Shiju Samuel ...more >>

I try to create an index that references three columns in a table: --------------------------------------------------------------------------------------- CREATE UNIQUE NONCLUSTERED INDEX [AK_DataReadyStateCache_Unique] ON [dbo].[DataReadyStateCache] ( [DataReadyStateTypeID] ASC, [Questi...more >>

I am joining 2 tables using a bunch of criteria - I also want to check for the existence of data from one field in one table to another field in the other table e.g I have "streetaddress" in one table "Barton", the other table has "address" field "1020 BARTON RD". I want to match if stre...more >>

Hi, I am using MS SQL2000. I tried to use the query to do the following. Why does it not work out? I could not get DateAdd("d",-1,Today) work correctly. select distinct a, count(*) from test where [timestamp] > DateAdd("d",-1,Today) group by a Thanks a lot for your help!! Michael ...more >>

Hello, I have a table where one field is a part number. These part numbers consist of a "base" number, then a dash ("-") and then a dash number. Examples: 300-102, 1534-32, 98872-12345. I would like to create a SP where I pass it a base number and it returns the next sequentially available...more >>

Hi, changing the database per name is no problem USE master GO .... and everything is okay. But i need this in a script and have the database name in a variable. How to make this? select @cmd = N'use ' + @oldDb + N';GO' exec sp_executesql @cmd doesn't work. Is something possible a...more >>

I'm trying to create a mailing list using a query. Simple enough. I have two tables. One is 'Practices' (doctor's offices), a parent table, and the other, 'Members', is a child table that has all the people who work for these practices. There is address information in both tables. If an ind...more >>

Hello, I've implemented the sample Printer Report Delivery extension in my installation of SSRS (SQL Server 2000). However, for some reason the Printer option doesn't show up in the combo box of the Report Delivery Options. Any ideas what I should look for? According to the tutorial, all...more >>

Hi All, I've been trying to implement the sample Report Services Printer Delivery extension. I've been through the tutorial and haven't been able to add the following security code group (shown at the bottom) successfully, since there is no indication of where this should go or why. I not...more >>

hey all, select * from categories where categoryid = 1 or categoryid = 2 what's the best way to create a stored procedure that would accept a variable amount of categories for the where clause? for example if i just wanted to search for categoryid = 1 instead of both 1 and 2. thank...more >>

I'm using Sql Server 2005. I have a table with two columns; PropertyName and PropertyValue. PropertyName has values like LastName, FirstName, Title and PropertyValue has the valus for those PropertyNames. Ex: AccountName Doe FirstName John LastName Doe Manager Jane Smith AccountName Cram...more >>

I had a development system crash (OS HDD failed) and had to reload things recently. My data drive and backups of that were intact. I reinstalled SQL 2k and tried to reattach the mdf files. Here's what I did in QA: EXEC sp_attach_single_file_db @dbname = N'Caser', @physname = N'D:\Data\MSSQL\Da...more >>

how can iwe identify tht the page is post back? ...more >>

I have a string type of column called 'Question' in a table, DataReadyStateCache. Each record for this column contains 7 commas (',') in the middle of the string. Each record for this column is in the format of: substring1,substring2,substring3,substring4,substring5,substring6,substring= =AD ...more >>

i have to find 7 th highest salary in a table. i use order byb clause in a sub quarry? is it work? ...more >>

Hi the BOL DDL trigger below is it possible to add rollback to it and still see changes that people have made to the database schema. I have tried with the rollback and nothing is entered onto the ddl_log table. I would like to capture all events even if rolled back is this possible. thank...more >>

I have a table of 10,000 rows .i have to made 1 cluster index and some non cluster index in that table.what i should make first i.e. made cluster index first or make non cluster index? ...more >>

I have whole bunch of rows in a table that contain 7 commas (',') as a separator. Each row is in the form of: substring1,substring2,substring3,substring4,substring5,substring6,substring7,substring8 I'll need to replace the LAST ',' with a '=' for each row of data. What's the most efficient S...more >>

Does anyone know if Microsofts' obsolesence policy still holds? That is, when a product is 2 versions behind, it is no longer supported with SP's and hotfixes? So, when SQL 2008 comes out, will SQL 2000 still be supported? I'd certainly expect that PSS would still take call on it, but most of...more >>

hey all, i'm doing a full backup from SQL Management studio and when i go to restore that database on a different server my user-defined login doesn't show up. I have to delete the old login which shows up in the database users and then recreate the user in Logins and remap to database. ...more >>

SQL Server 2000 I have a simple select statement to calculate the average: SELECT avg(duration) FROM tablename The problem is the average is being rounded incorrectly. For example, if I am calculating the average of 718, 721, 1032 the avg is 823.6. However, the avg is coming out to 8...more >>

Hi all, I am unable to export a small table (50 rows) into an excel on a remote = machine (called MyPC in this case). I run the following code in a Query = Analyzer: SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel = 8.0;Database=3D\\MyPC\testshare\testing_new.xls;', 'Select * From ...more >>

How can I create a query for the following that will return results for say; * 'Phoned' and not 'Ordered' -> ('Harry') * 'Phoned' and not 'Invoiced' -> ('Dick' & 'Harry') - Jamie CREATE DATABASE [test_db] GO use [test_db] GO CREATE TABLE [dbo].[Customer] ( [id_Custom...more >>

When running this huge select statement, I got an error: "Invalid length parameter passed to the substring function" The statement is as below: --------------------------------------------------------------------------------------------------------------- SELECT DataReadyStateCacheID, ...more >>

hello I use MS SQL server 1) Example of a table with compound primary key would be: CREATE TABLE A ( b integer, c char(5), d integer, PRIMARY KEY ( b, c ) ); But how do we create compound for...more >>

I am trying to update a column that has the XML datatype. I get this error XML parsing: line 4, character 16, unexpected end of input when executing the sql below. What am i doing wrong? declare @temp nvarchar(100) set @temp =N'<books> <book name="Golf for dummies" /> <book name="Footba...more >>

hello, I have the following problem: data info: Table1.Volume2 is a char(11) field (data looks like 50, 2, 3, 0.05, 0.12) Lookup1.Volume is a char(11) field (data looks like: 00000162000, 22000000000, 02400000000) The value of Lookup1.Volume is usually divided by 1000 and should look...more >>

I'm using SQL Sever Profiler to trace the stored procedures being exected on the server. I want to filter out the stored procedures that are frenquently executed yet not related to the issue. What's the exact syntax for setting filters in "Not like"? There's "Edit Filter" dialog. I select "Tex...more >>

Hi, I'm trying to join two tables. The table user and the table logs. A user can have millions of logs. When I try to join a user and its logs, the join is really slow. I suspect the problem is that the logs table return 4 millions of rows. I can't really diminish that number and it's th...more >>

I'm studying the fix to a bug about "OSQL.exe" utility. It's BUG #: 471423 - The Osql.exe utility does not run a Transact-SQL script completely if you start the program from a remote session by using a background service and then log off the console session What happened was that I ran a sche...more >>

Hi all, I've a problem and I'm unable to solve it. I wrote a trigger like that: ALTER TRIGGER reminder ON USERS AFTER UPDATE AS IF (UPDATE(PASSWORD)) BEGIN UPDATE LOGS SET LOGS.DESCRIPTION = 'Password changed' WHERE LOGS.IDUSER = ??? END; GO I don't understand where to take ...more >>

Hi, Can any body tel me how to get list of files and file groups available in a database thro sql query? thanks in advance. Prince ...more >>

I have the following script: ------------------------------------------------------------------------------------------------------- DECLARE @liError int -- Add three new columns ALTER TABLE SystemParameter ADD Threshold int, -- Could be either number for count threshold or minutes C...more >>

I think I'm on the right track here but cant get it all the way. Maybe someone can point me in the right direction from what i have started. First here are the tables: CREATE TABLE [dbo].[bt] ( [idtrans] [int] IDENTITY (1, 1) NOT NULL , [idrma] [int] NOT NULL , [phmodel] [nvarchar] (30) ...more >>

Hi, I was wondering if anyway could throw any suggestions on the following and why one query was taking so much longer than the other. The queries come from a data warehouse where the fact table is 230million rows. It joins onto a time dimension but then also onto a BOW_DATE_RANGE_V view whic...more >>

Hello, Thanks for reviewing my question. I would like to know how I can write a query where one column increases in value as query is displayed. (Like a row number) NAME ID John 1 Bill 2 Frank 3 I tried using ROW_NUMBER() but it only display one each time. ...more >>

Hi I've a stored procedure in dynamic sql, that produces some sql query, that works well, and produces de right results, but now since the the interval of Id's grow up, had poor performance and causes the system slow, to other queries. It's a sql server 2005 Enterprise Edition. This is th...more >>

hi, Is there any function available in SQL Server 2005 to determine the Non-English characters in data fields? I have lots of data in my tables and I want to change some of my data types from nvarchar to varchar, that’s why I want to know if the Non-English characters are exists in data fil...more >>

Dear all, I have two table call Invoice and Shipper just as follows Table Invoice ID Field-ABC 1 A 2 B Table Shipper ID ...more >>

Hello, can anyone pls help on this update, i can't figure it out! I have 3 tables TableA InvoiceID - Identity FileID - INT OpNo - INT TableB PKey - Identity InvoiceID - FKey to TableA InvoiceID ItemID - INT TValue - Decimal TableC PKey - INT Identity FileID - INT OpNo - INT It...more >>

Hello everyone, we have a large application (aspnet and dotnet) which uses a quite complex database (109 tables). I have been asked to optimize query times that (on some tables which can grow quite big) is becoming slower and slower... At the moment we only have primary keys on autogene...more >>

I have a table that has more than 54 million records and I'm searching records using the LIKE statement. I looking for ways to optimize/partition/etc. this table. This is the table structure: TABLE "SEARCHCACHE" Fields: - searchType int - searchField int - value varchar(500) - externa...more >>

Morning, I am using this stored procedure to recieve a xml doc as parameter and then shredding it to insert records, all works well apart from the AGENTID as it keeps writing NULL values in the table. Been trying to find errror in my syntax for while but with no luck. I hope you guys can help. ...more >>

In stored procedure, i want to replace serveral character in the string. E.lg ="HKG-123333", REPLACE TO "LAX-12333" i don't want to use left(), any other function can use? Thanks a lot ...more >>

I posted this before, but couldn’t find it. Here is the question: Use Northwind, how to query out: Employee.EmployeeID, Employee.LastName, Employee.FirstName, Max(Orders.OrderDate), Orders.ShipName -> Correspond to Max(OrderDate) entry Thanks... ...more >>

Hi, I posted this before, but couldn’t find it. Here is the question: Use Northwind, how to query out: Employee.EmployeeID, Employee.LastName, Employee.FirstName, Max(Orders.OrderDate), Orders.ShipName -> Correspond to Max(OrderDate) entry Thanks... ...more >>