I have looked around and found the equivalent for IIF (Access) to be a SELECT CASE in SQL. I have tried this with no success. I am also looking for the equivalent of MAX and have had no luck. The portion of the string I am trying to SQL'ize is: SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])...more >>

Hi Guru, My company, every thing we need is to buy from a vendor. However, we have PEOPLESOFT CRM app that has around 6000 tables and around 5000 views and none stored proc. We start seeing the slowness of the app. When I started running a trace to capture some data, and there is no useful in...more >>

Hi all, I have a tempdb that consists of 8 datafiles, tempdb_data_1 to tempdb_data_8, each is 8GB. Now how can I drop 7 of them and leave only tempdb_data_1? Can this be done? Thanks a lot. ...more >>

I have data like this in a two column temporary table - ID Age 23586 3 23586 3 23586 2 23586 2 23586 1 23586 1 23586 1 23586 1 23586 1 I need to create a temporary table that look like this: ID ...more >>

Hi, I have a table A (ID, time,...) first I want to select rows with max value of time. Then from these rows I want the row with max ID value. i am doing the following but its giving me the error mentioned below select max(ID) from (select * from A where time in ( select max(time) from ...more >>

Hi All, When I use bcp APIs to import data into sqlserver database, after batch rows to the tableA, I want to import data into tableB, so I invoke bcp_init again to init tableB, but it can't success. unless I re-connect database and then do bcp_init. But re-connect db will waste some time. My ...more >>

Hi all, I know that you can save a DTS package to SQL Server (local package under Data Transformation Services in the EM). I wonder can I limit which login has the right to save DTS package? I mean, I would like logins with sa right to have this right but not for other ordinary logins. Ca...more >>

Sorry re-posted as my email setting were wrong on the last post I wonder if anyone can help. I have a scheduled job running overnight to delete old records for a particular Db table. The table contains more than half million records and the script simply uses the date field to delete...more >>

I wonder if anyone can help. I have a scheduled job running overnight to delete old records for a particular Db table. The table contains more than half million records and the script simply uses the date field to delete any of the records which have a date older than 7 days. My guess is t...more >>

One way to save storage space is to put the SQL data files into a compressed file. Has anyone got any idea how this will affect the query speed? ...more >>

<% if UBOUND(Arr) > 0 then%> <% end if %> I am getting the following error: Error Type: Microsoft VBScript runtime (0x800A000D) Type mismatch: 'UBOUND' How do I solve the problem. Your help is kindly appreciated. Eugene Anthony *** Sent via Developersdex http://www.developersdex...more >>

This asp code displayes records in a combo box: <% openDB() call updateDB("usp_retrieveOptions",rs) if not rs.eof then %> <tr> <td width="66">Options</td> <td width="137"> <select name="...more >>

Hi, On my SQL Server 2000, I have a table of data (tblAllData) containing a number of columns, some of which are 'secret'. I have to let some users access the database using ODBC from an Excel sheet, and I would like that they do not know at all that the columns exist. I tried creating a view...more >>

I have to create a view where the filename is seperated from the path to the file. Examples of the data include: m:\images\big\myimg.jpg m:\images\medium\myimg.jpg z:\media\images\highqual\myimg.jpg Is there a function that will return the position in a string of the last \ ? With th...more >>

I want to add bulkadmin permission to my applicatio role. Is it a posible. My windows account havo only public permission on database. I'm using application role EXEC sp_approlepassword 'MyRole', 'password'; Therefore I want to BULK some data with BULK INSERT command. Error is: The cu...more >>

Hi, Is there a simple way to verify if a database exists? I'm writing a stored procedure that will accept a database name as an input parameter, and create the database if it does't already exist. -- Message posted via http://www.sqlmonster.com...more >>

when I run the following in sql Analyzer : dtsrun /S"Local" /Ntestemail /UExternalRO /P"changmail" got this error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '/'. Is the command right?And how to run this dts package in commandline? Thanks...more >>

Hello sir We have a very huge database its around 6 lakh records are being stored in it. records are not being a sorted order so we checked all record field through clustering option in Sql server.when we used clustering records are showing in sorted order but speed ...more >>

Hi group, In one of the books 'Gurus Guide to Transact SQL' i found this info: ------------------------------------------------------------ TRUNCATE TABLE empties a table without logging row deletions in the transaction log. It can't be used with tables referenced by FOREIGN KEY constraint...more >>

Hi, I've 2 Databases one is online and one is offline (connected t Internet).. using SQL SERVER for both servers, i need to update th OFFLINE Database one on a time schedule for every 15 mins from th Online DATABASE .. can anybody suggest a better way other than web services.. coz ou serv...more >>

I have a .NET application that needs to work with about 5,000,000 XML files 5Kb each. Mostly the application randomly reads these files and it adds/modifies about 100 files every 10 minutes. What would be the best data storage in tirms of performance (connection, search and retrieval times) a bi...more >>

Currently I'm using a UDF and a physical temp table to accomplish this and I want to know if there's any way of doing it in a query or subquery instead... In TableA there are a bunch of rows: InvitationID (PK) PartyID Partygoer 1 1 Jim 2 ...more >>

we have a table like this OrderNo OrderDate 1 2005-11-04 01:12:47.000 2 2005-11-19 04:26:54.000 3 2005-11-16 11:03:23.000 4 2005-11-21 15:58:37.000 5 2005-11-24 21:45:04.000 what will be the sql query, so that the Result look like this. only to neqlect the time factor fr...more >>

ARRRRRRRGGGGGHHHHH!! Please can you help, I'm going round the bend with this. I have a simple and small table called STOCKCATS, which I need to query to get back a dataset in a particular order, but although it looks simple I can't get it to work. My table schema plus sample data to see the...more >>

I have a table on the database with columns like the following: Name Date Data Joe 11/5/05 data1 Joe 11/6/05 data2 Bob 11/5/05 data3 Bob 11/8/05 data4 I want to retrieve all data from an...more >>

Hi Guru, My profiler trace does not display SP:CACHEMISS event, even thought I drop store proc, clear both data cache and buffer cache but still does not work. Every thing works fine like: cachehit, cacheinsert,cacheremove,executecontexthit etc... Is there any special option that I need ...more >>

Hi, I have a sql server database with 1.7 million records in a table, with about 30 fields When I run select * from tablename it can take over 5 minutes. How can I get this time down or is it normal? Thanks Jerry ...more >>

Dear (and mighty) all: I backed up a database (SQL server 7.0) and tried to restore it on another system (SQL Server 2000). This is not the first time I'm doing this and never had a problem before: -- in the source db BACKUP db_icoaraci TO DISK = 'C:\TEMP\BACKUP-ICOARACI.DAT' --in the de...more >>

Hi, I am very new to Microsoft Technologies, infact new to database world :)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i restore this backup by using SQL-Server 2000Enterprise edition, how should i go about it? Any help or comments will be highly appreciated. -Erland ...more >>

Hello group, I have a doubt on where use inner join and use the signal of equal. Is there any difference in performance? Where is the best? Thanx Marcelo Sabino ...more >>

I am not a DBA and would appreciate any help with the following question Is there any easy method to search all tables in a given database for a particular value ? I am using sql server 200 standard edition all help appreciated Thanks ...more >>

I am trying to import data from Access 2000 in SQL Server 2000 using DTS. One of the tasks requires a multi-table join but I am getting syntax errors if I generate the query with Build Query. With just a single join like this it works fine: FROM Tracker INNER JOIN ...more >>

Hi. Sorry if I am asking a stupid question since I am an absolutely beginner in SQL Server. Here is the question . . . About 13 hours ago, I got my SQL Server 2000 to index a table which has 104 million records. At first the CPU usage was high. But after an hour or two, the process has seem...more >>

I am trying to create a stored procedure whose where clause is dependent on a parameter. If the parameter @myparam is null or '' then I want the where clause to be one thing, else I want it to be a completely different thing. I can do it easily using iif but obviously that is not an option he...more >>

I am trying to create a stored procedure whose where clause is dependent on a parameter. If the parameter @myparam is null or '' then I want the where clause to be one thing, else I want it to be a completely different thing. I can do it easily using iif but obviously that is not an option he...more >>

Hi, When I pass a date time parameter the stored procedure takes about 45 seconds, when I hard code the parameter it returns in 1 second. How can I rewrite my stored procedure? @createddatelower datetime WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower ) AND dbo.tblCa...more >>

Hi i need help with formulating a query that will update the a field on one table depending on the values from another for example i have a cart table: cartid, buyerid, productid, quantity i have a product table: productid, quantity i want to do a: select * from cart where buyerid=x...more >>

I would like a single SQL to return all employee's total billable compensation for a year. Their billable rates change throughout the year so under the employee table (one), there is a compensation table (to many) which has the employee id, effective date, billable hourly rate. So in a given...more >>

Hi, In my stored procedure I'm doing a SELECT on INFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id on this table, so I was wondering if it was possible to add it dynamically in my SELECT, so that I would assign a unique id to each record returned by my SELECT? Thanks for yo...more >>

Hi, I have SQL SERVER 2000 SP4 Enterprise , Windows 2003 Enterprise on Xeon 4 Processors (now with multi-threading CPU) and I have questions which seem weirds to me (used to have the same config without Multi-Threading) as following: 1. SQL Server s...more >>

Can any one help - my Full text catalog on a remote shared sql server has died and i need to recreate it completely - I have done this before but i've lost the code to do it. If I remember right what i did was use start --> run --> to run an exe in the mssql folder that connected to the re...more >>

I have a question when I work on Linkedserver The Linkedserver name is [Hp-server],the Datebase name is Newexec,the Table name is Customers_CoypTest The SQLScript is below: Update [Hp-server].Newexec.dbo.Customers_CoypTest set Unitname=b.Unitname ...more >>

I am having a problem with indexes on specific tables. For some reason a query that runs against a view is not selecting the correct index on a table. I run the same query against the table directly and it looks fine. Can anyone give me some insight? Thanks. PRODUCTION1: CREATE TABLE MyTest1 ...more >>

Hi, all. I am having some confusion with connecting to a SQL 2000 SP4 Server across a one-way trust. I have a SQL server in the trusting domain, and the Admin workstations in the trusted domain. I am not using domain-level authentication, I am only using SQL IDs. If I log onto the work...more >>

I have the following table CREATE TABLE Readings ( ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY, Reading int NOT NULL ) INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1) INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12) INSERT ...more >>

Hi all is there any efficient way to get the T-SQL statement for an existing index? In the EM, we can 1) right-click on a tablename -- All Tasks -- Manage Indexes; 2) select an index, then click on "Edit"; 3) Click on "Edit SQL" to get the SQL for the indexes as below CREATE UNIQUE ...more >>

Hi, I have installed SQL 2005 RTM on a new server and I keep getting this error (described below) quite frequently. Was wondering if anyone has a clue on what's happening here. I tried googling but no success! This generally happens when I am browsing the tree in Object explorer in SQL 2005...more >>

I detached a SLQ 2000 database and reattached it in 2005 Developers. When I try to access the Diagrams, the below message is received. My domain account is owner. I'm local Admin on my machine. Shouldn't be a problem right? How do I add the Database Diagram Support Objects? TITLE: Micr...more >>

Hi guys, Got a problem now :( please help... now we got a project handling records saved in a table in a sql 2000(will upgraded to 2005 soon) server. every month around a million records will be inserted. now user raised a request, that is, once criterios are matched, the project should ...more >>

Hi all, I'm fairly new to this... I't trying to design a DTS package on SQL Server 2000`, which will connect to the server, export some table date into a .txt file (based on a select statement) and then delete the data from the table (based on a delete statement) upon successful completion of...more >>

Hi All I know that if SQL 6.5 was on a server and you then install SQL 2000 on it you get the wizard option to upgrade an SQL 6.5 DB to 2000, but I don't have the luxury of this. I have an SQL 6.5 DB, which I want to convert, but only SQL 2000 on my server. Do you know what options I hav...more >>

Hello, I have an "Issue Manager" script that I would like to run on my SQL Server (2000). Being a novice, I'm not sure how to do this. I can't seem to find anything in the help file that makes sense to me, and I'm sure it's because my search strings suck, they only produce a single result...more >>

I have the following tables: Table Name: GL_CODE_DESC Field Names: GLCODE, GLDESC Table Name: GL_SVC_CODES Field Names: GLCODE, SVCCODE What I would like is a query that pulls the distinct rows from the table GL_CODE_DESC but only where the GLCODES are equal between the two tables. H...more >>

Is there a limitation of using: set @sessionID = NEWID() would there be a simular NEWID() being generated if used in a database application. Eugene Anthony *** Sent via Developersdex http://www.developersdex.com ***...more >>