Part of one of my queries performs an index scan (generates 1.8M rows), and then filters that set (down to 61 rows). The plan text follows. My question is (because i just don't know) shouldn't an IndexScan followed by a Filter operation be able to be converted into an index seek? ...more >>

After searching for a while on the net I realized that paging in t-sql is one of the most debatable topics among SQL programmers. There are lots of different suggestions depending on the performance and usability but I couldn't find a document which lists all different methods and their pros & c...more >>

Hello Friends Pls. help me in this. I want to convert the oracle procedure in the T-SQL. And i am stuck in converting the %RowType statement in T-SQL. Can anyonce suggest me how to do this?...more >>

Hello Friends Please help me in my following queries 1. What is the Table data type in T-SQL 2. Where can we use it? (pls. suggest the syntax 3. Can the Table data type be a replacement for the %RowType in Oracle....more >>

I'm using SQL server 2k on a shared server. I need to calculate, in realtime, the actual disk space used by a portion of my "Products" table (e.g. finding how much space is used by products belonging to a specific category). Can I retrieve this info from within a classic ASP/VBscript app? Any h...more >>

Hi, As a ETL tool we are using DTS to load dimensions and measures. We have some ten packages with each having many tasks. There are some functions like FormatDate (in VB Script) which I have written and are required in alomost all the tasks. But I'm ending up in including the function in e...more >>

Hello, I am trying to use left outer join with filters, USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a LEFT OUTER JOIN publishers AS p ON a.city = p.city WHERE p.pub_name='Algodata Infosystems' Go But due to some reporting tool restructions, the query is genera...more >>

I have two tables, one for currently employed hostess (ACTIVE_HOSTESS) and another for resigned hostess (RESIGNED_HOSTESS). Both table has a field ID_NO as the key to each hostess. Now using @ID_NO as a search key, look for record in ACTIVE_HOSTESS; if not found, look into RESIGNED_HOSTESS. ...more >>

Hi There, I am very new to SQL Server and would like to know somw syntax for basic error routines if possible? Also is it better to have an error routine in each stored procedure and use try / catch in the page or a combination of both? Sean - thanks in advance CREATE PROCEDURE Custom...more >>

Hi all! Im trying to get a query that brings me the difference between two dates but ONLY LABOR DAYS ie Today is May, Wednesday 26th and the target date is Friday Jun 4th. an I want to get th difference without Saturday and Sunda Thanks in advanced Sergio...more >>

I browsed trough newsgroups, esp this one, found a lot of info about transactions, but I still have some doubts. So, can I pull something like this: I have this, 'main' procedure: CREATE PROCEDURE mainProc AS BEGIN TRANSACTION <do_some_work> EXEC workProc <do_some_more_work> IF <some...more >>

Can someone help with this creation? I am trying to take a parameter input value as a from variable. I am getting an error as @InputTable not defined. ??? CREATE FUNCTION EmployeeIDCheck (@InputTable varchar (50)) RETURNS Table A Return (Select * from @InputTable Where EmployeeID = '9999' ...more >>

I have a query as follow: SELECT CONVERT(char(10),EndedDateTime,104) as DateCompleted FROM MYTABLE I want when there is one record this statment retrun one day before and after that ,,for istance if there is only one record in mytable like 2/3/2004,the query returns 2/2/2004 2/3/2004 <-...more >>

I have a SPROC that I want to pass a single date variable like 5/24/2004 and return records from 5/24/2004 5:30 AM to 5/25/2004 5:29.59 AM. FIGURE 1 runs find with datetime hardcoded in the WHERE clause. I need help building my where clause in FIGURE 2 so the SPROC concatenates the 5:30 AM and...more >>

Hi all, I have a table with (among others) two columns in it-- an ID field, and a "parentID" field. We use this table to draw a folder tree on our web pages. Sometimes we run into a problem where the child accidentally becomes the parent to *its* parent, creating a bit of a circu...more >>

Just upgraded from SQL Server 7.0 Entperprise Edition to SQL 2000 EE sp3a using an inserver upgrade. Windows 2000 Adanced Server SP3. Everything working OK with hundreds of users attached except for intermittent Access Violation - see dump below. I cannot reproduce the problem but I sus...more >>

HI there, I am trying to test a return value from a stored procedure, I pass 3 parameters in and I want to return an error message if the username already exists in the database, what am I doing wrong? Sean - Thanks in advance for your answer !-- stored rocedure CREATE PROCEDURE Custo...more >>

I upsized an Access 2000 dB to SQL Server 2000 I am trying to filter records from one table [Exams] in a "CustomID" field from another table's "CustomID" field ([CustomInfo] table) Before I upsized, I was using the fileds as "text". After upsizing, they are now "nvarchar" The primary key in Cus...more >>

Hello I pass a char-variable @Order and it doesnt work if column is datetime....... SELECT ......... ORDER BY CASE @Order WHEN 'SUBJECT' THEN S.Subject WHEN 'USER' THEN U.Firstname WHEN 'DATE' THEN Q1.Datecreated END I get: Syntax error converting ...more >>

Hi folks, I am having a unique problem here. I have this table where I am storing information in 3 columns: * Question - Stores questions submitted for review * Keyword - Stores keywords entered by expert * Response - Stores reply entered by expert I want to do a search on this table on ...more >>

select round(((1.0 * t2.TotalCanc) / t1.NewAcc), 4) * 100 as 'Total%' returns 34.690000000000 how can i get it to just return 34.69??? ...more >>

Hi, I have a job on sql 7 server that stuck at 'perform completion action' step after we stopped the job. Is reboot the server the only choice to fix it? many thanks. JJ...more >>

i'd like to use smalldate as datatype for version validation but don't know how to use it. can someone help me with this or point me to where i can do some reading? thanks tin ...more >>

Here is the overview of the situation. A bus arrives at a certain time, and is slotted to park in a certain Bay (i.e. parking spot). Each bus allocated to that Bay for certain time period (i.e. 15 minutes). A Reservation is a bus arriving at a certain time, using a specific bay. My query is...more >>

Hi all I'm new to SQL, so please forgive me if this is an elementary question I've been having trouble doing positional updates and deletes using a cursor. I checked out BOL and it said "Before a cursor can be used by a positioned UPDATE or DELETE statement, the SELECT statement in the cursor...more >>

Sql Server 2000. Is it possible to use a nested IF statement (or even an unnested IF statement)when defining a Column Formula, such as: IF(Funding_Type="FED",[2004 Hours]*22.50,(IF(Funding_Type="ST",[2004 Hours]*21.24,(IF(Funding_Type="CITY",[2004 Hours]*21.75))))) Thanks, Dale Kahn...more >>

I have the following procedure: declare @BeginDate SmallDateTime declare @EndDate SmallDateTime set @BeginDate = '2004-05-01' set @EndDate = '2004-05-31' select CL.caldate, (select count(*) from seniors inner join permissions on PE_SNID = SN_ID and PE_PLID = 1 and PE_Begin <= CL.caldate ...more >>

Why doesn't the convert style method work. The ",1" in the convert statement below is suppose to convert the 1000 to 1,000.00 Thanks in advance. declare @gift as char(15) set @gift = '1000' select convert(money,@gift,1) ...more >>

I am writing a job to update statistics on my database (yes, I know about auto stats). For very large tables (over 1 million rows), I'd like to use a larger sampling than for small tables. Here is a code snippet (@table_name is filled in earlier in my script): if (select max(rows) from sy...more >>

I have the following SP for which I am trying to establish new records in three tables: Facility, Address, and the connecting table FacilityAddress. I call this from ADO code but get an error message that I am not supplying the NewAddressId or NewFacilityID. But I am generating those values from thi...more >>

I have an update trigger that uses the "IF Update (column)" function. The trigger sends out an email containing some information from that record. When an update is made to that column of ANY record, for some reason, the trigger always fires on the last record in the table even though th...more >>

Hi all, I am getting the following error on an insert query. Because the script is too large and the query is complex, I tried to reproduce the error on a simpler script written to test it and post it here. But I did not get an error in the test script. "Only text pointers are allowed in ...more >>

Hello there, I'm having the following trouble and I hope somebody can help me. I have a table with the Key field as a Identity Field [auto increment] and, at the same time I'm inserting a new Value in this table, I want to use this ID field. The "INSERT INTO " then "SELECT MAX(K...more >>

is it possible to store a text file in sql db? i wanted to store my firewall config (either just content or text file) in mssql db so i can retrieve/update from query gui. thanks ...more >>

I would like the 20000 in the SELECT TOP statement below to be passed into the stored procedure and to be used inside a declare cursor. I have been unable to do this using by passing a variable, declaring a variable or putting the entire select statement in a string. Of course it works outside ...more >>

Hi All, I've used uniqueidentifier extensively as a clustered primary key column data type. I've seen a lot of discussions whether or not GUID ought to be used as a primary key. Despite to the notion that it takes whole 16 bytes I am satisfied with the general query performance on a 6GB+ datab...more >>

Hi all and thanks in advance, I was wondering what is the best way to find out the ID's (Identity fields) of a batch insert into a table right after inserting? So that I can only select the rows just inserted. by immidately after an insert I mean in the same stored procedure and the next comm...more >>

Is there a way to force a Insert/Update trigger to fire after an insert or update of several records? I'm redeveloping a application where the records used to be Inserted one at a time and the trigger executed fine. The redesign inserts or updates several records at a time and the trigg...more >>

I've got a problem. I have a table that mysteriously has had data deleted from it. So I figured I would put a trigger in place to force it to not allow deletes. The exception, is if the record is deleted as part of a cascading delete. Unfortunately, I cannot figure out how to code this. T...more >>

Hi, Recently when creating/altering (mostly altering) tables in any database on a particular server that we have I have been getting the following error 'tlkpRegion' table - Unable to create index 'PK_tlkpRegion'. ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state Origin...more >>

I have a simple update trigger that updates a table. I need to turn on recursive triggers for another one I'm working one. Obviously the first trigger is going to recurse until the nesting level is exceeded. How can I resolve this? Thanks a lot, Jason ...more >>

Hello All, I need some help in formatting a decimal column I have in my SQL DB. The column is called RATE, DECIMAL precision=8 scale=5. How can I write a SELECT statement that will return this column to a fixed width with zeroes padded to left and/or right. For example, value in DB n...more >>

thanks for any help with this. i have a vw that used to time out. i used the index tuning wizard in query analyzer to create an index on one of my big tables (2M records) and everything worked great thereafter. when i run the views now, it times out. when i try using the index tuning wizar...more >>

Hello, I'm using SQL Server 2000. I'm trying to write what the help file calls a "left anti semi join", that is to return all the lines from the left table that do not match any in the right table. The only way I found to write that is something like : select * from T1 left join T2 on...more >>

All, I am kicking the first part of a rather complicated ETL process off by loading a flat file into a Import table. At some point I must convert the some of the data from the char data type that is imported as, into int, money, and a few other data types. The question then becomes, do I do...more >>

Hi every body .... I have developed a projet ( windows application ) whit visual studio .net ( vb) and sql 2000 ( desktop edition ) when I want to debug my VB code I set a break point and run my program ...It stops at my breakpoint and it works well .. but I want to debug my sql code whitin my vi...more >>

First of all apologies for posting this message again, reason i am posting again is because it was extremely close to being answered last time and would just like to but it back on the radar Basically my question was I am trying to identify a way a grouping data into contiguous blocks, for exa...more >>

We are passing XML to a Stored Procedure. We are trying to use the OpenXML ....WITH table-name One of the columns coming in for an UPDATE is one that is an Identity column. We are having problems saving the Stored Procedure. The error is Error 207: Invalid column name 'QM_ID'. ...more >>

Hi, I am adding a MEMO function to our system and was wondering which DataType is best to use to store the text and still be able to search it via an ASP/SQL form? I was under the impression that you could not search certain DataTypes? The memo fields will not be holding huge amounts of te...more >>

Hi Y'all I am new to these two types of objects and can use some help getting started. I need script that will take any letters off the end of a text strings like 2000-020A to return 2000-020 and then search a table to count the number of records that contain this string in a particular colu...more >>

I have this on ASP, the member signs up with this code. Set Conn = Server.CreateObject("ADODB.Connection") Conn.open strconn login = Trim(request.form("login")) 'password= Cstr(EncryptString(Trim(request.form("password")))) Randomize RandomNumber = Int(Rnd * 100000) + 1 activac...more >>

Hello I have an SQL file with several SQL commands I want to execute this SQL File from Query Analiser without open it Can i use some SQL command to do it Please Help Thank you......more >>

Hi Everyone Was wondering whether anyone could point me in the right direction. I am trying to identify a way a grouping data into contiguous blocks, for example say I had the following table with one column and the following values Valu 1 What I would like to be able to do i...more >>

Howdy all, I know relatively nothing about financial database design (which is eveident from my own checking account management). Is there a place where one can download example schemas of financial/billing/receipt type systems. The simpler the better. Thanks. Brian...more >>

Hell What is the correct syntax for backing up the transaction log with truncate only option to a mapped drive on the network I tried BACKUP LOG DB_NAME TO DISK='F:\TRANSLOG' WITH TRUNCATE_ONLY and I got a syntax error Thanks a lot!...more >>

hell I tried shrinking the transanction log file, first I used bcc opentran and there were no open files, than I tried dbcc shrinkfile (logfilename, 2) and I got the message that I cannot shrink the file as all logical files are in use!!! How come nothing showed up in dbcc opentran?...more >>

Dear All, Due to reasons too long to explain our identity columns are getting out of sych. Using the command SELECT name from sysobjects where xtype = 'U' i would like to loop though all my tables and update the internal value (not the one on the table but the one held if you do a IDEN...more >>

Hi I'm using the following query..however, it doesn't show me "all" the unique keys only when i use the 'where' condition(but includes all the pk and fk's as well) and i'm forced to use the Like condition..Am I doing it right? --lists all the unique constraints of the database and the columns on ...more >>

H I guess there is no absolute answer to this question but here goes... I am building a 'tracking system'. It tracks movement and usage of stock items (at this stage) and I need to maintain movement / usage history and 'current' stock levels I am consideing 2 alternative ways to implement the up...more >>

Hi, I'd like to extend my EM MMC and add new functionality. Is it posible - if yes how to do this. New option in Context Menu. Best regards Artur ...more >>