I was looking into ways to calculate running sums in TSQL. (I know, maybe it belongs in the app, and not in the DB). Self joins and correlated sub-queries in the SELECT list both work, but they are slow. Poking around the web I found this interesting UPDATE statement that is able to pro...more >>

I am try to restore a database from external hard drive and I am get the errors below. Do you have any ideas how to resolve these errors?. Thank you in advance. Errors: Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SCData_p...more >>

Hi, I have If condition that evaluate always to ture and I can't figure out why .. Any help will be most appeciated. I have placed a comment next to the If statement mentioned ... ALTER TRIGGER TriggerInsertHotel ON dbo.Hotel_Res FOR INSERT /*, UPDATE DELETE */ AS /* IF UP...more >>

I am new to SSIS and am creating my first package. Basically there are about 10 data flow tasks and each one takes quite a long time to run. I would like to be able to start the job at, say, the 5th task and continue to the end. Is this possible in the VS2005 debugger? It is easy enough to execu...more >>

I am trying to get information out of a record in a table which is stored using XML. e.g. xmlformdata: <DATA> <CHILDDEV_CHILDSERVICESUM_9_STAFF_TEAM lookup="1">CDTEAM_OT</ CHILDDEV_CHILDSERVICESUM_9_STAFF_TEAM> <CHILDDEV_CHILDSERVICESUM_9_STAFF_NAME lookup="1">CHLDEVSTAF_DOUGLAS</ CHILD...more >>

When I script table I receive slightly different command on each machine tested. Problem is with default values of primary key index. One machine scripts it with this options: WITH (IGNORE_DUP_KEY = OFF) another one with: WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY ...more >>

Hi, I have a script that creates fomr jobs etc. I would like to create this into a stored procedure so that new jobs can be created by running the SP. It is a long script and I was wondering if anyone can recommend any utilities to convert the TSQL for the job creation process into embedded...more >>

Hi, I created a DTS package. What it does is that use the following script to grad the line data from order table and export to an excel or text file. Script: [select ordernumber,invoicedate,custid,sales,prodid,description from order where pricecd ='COO'] Current Result is: ordernumber ...more >>

Hi all... I am looking for a way to relieve resource pressure on a very busy SQL Server 2000 until the hardware can be upgraded. Reading thru BOL I came across this statement: "Auditing can have a significant performance impact. If all audit counters are turned on for all objects, the pe...more >>

Being a newbie in SQL quesry I am ask to fix a problem in one of our company's database. Briefly, I have two tables with the following simplified schema: tblInventory intBarcodeId intQuality varBarcode tblNewInventory intBarcodeId intQuality varBarcode bolUpdat...more >>

Hi, I get sent backups from 1gb 4 gb but while restoring the database the transaction log can sometimes be over 40gbs. I know that the logs have never been backed up or shrunk before. But when backing up a 4gb database with a 40 gb transaction log: 1: Why does the database backup only s...more >>

How long can I run the profiler to get trace from the production server. It should not degrade the server performance. what is the minimum time to run the sql profiler to caputure all queries in the trace...more >>

We have to deal with getting a connection to a database that has a dash in the name (DA-Prod_data). We get error messages when trying to connect. Is there a restriction against dashes in the database name? Thanks. ...more >>

hey all, Given the following records for an employee: Start Date - End Date 8/16/07 - 8/17/07 --Position1 8/18/07 - 8/19/07 --Position2 8/21/07 - 8/22/07 --Rehire Position1 8/23/07 - null --Rehire Position2 All the information i have are dates and position name. Is there an easy way, ...more >>

Is there a way to recover a deleted function that I wrote, by pulling from the transaction log?...more >>

Hello, I have 2 tables: [Categories] > CategoryId, Category [Strings] > String I want to create a new table as follows: [CategoriesFound] > CategoryId, CategoryFound The "CategoryFound" column in this table is filled with all the "String" records in Strings. Then the Cat...more >>

Example: go to E:\MSSQL\BACKUP\SCData_accessmanagedservice folder and get file SCData_accessmanagedservice_backup_200709061759.bak......more >>

Hello, I've to implement a search engine, based on some parameters. A piece of schema is here: http://img301.imageshack.us/img301/585/senzanome2nj3.png The entity document can have N versions; a version could have N attachments or N attributes. The attribute is an entity defined in other two ta...more >>

Hi All, I have records like this ID VALUE 1 a 1 b 1 c 2 x 2 y 2 z I need to get like this ID VALUE 1 a,b,c 2 x,y,z Can this be done in single select or update I am aware of the way to do with a variable and coalesce function . But i need to do in sing...more >>

I can't seem to get the syntax for a query involving multiple (3) derived tables each with their own WHERE clause... I can get the 1st and 2nd derived tables to work...but can't add a 3rd....the problem is that I have to Filter the 1st derived table using a WHERE clause in each subsequent deri...more >>

Hi, I have two separate databases with exactly the same tabl structure and with almost the same amount of rows in both. My problem is that one of the databases are much larger than the other one (20mb to 21gb). i have looked at the table space allocation with sp_spaceused and saw that ...more >>

Hi all, I ran into a strange syntax issue using table variables. I want to do something like UPDATE @table SET var = var * (SELECT MAX(coefficient) FROM othertable o WHERE o.date <= @table.date) The problem is that I need to reference a column in the table variable and the...more >>

Hi, In my real code I use a varchar(max) variable to append problem messages for an error report. Without this error tracking my stored proc runs in 10 sec. With varchar(max) handling the proc returns after 14 min !! I downstripped the problem to the two procedures below. Here the benchmark ...more >>

Using SS2000 SP4. This problem is a little different than others that I've seen on the site. I have dates that look like 1/05/88 2/3/78 01/8/79 2/3/2007 07/08/1955 06/05/99 I want to change the column to smalldatetime and convert these dates. Is the best way to look for the second ...more >>

i have made a backup using a device in sql 2000. the problem is i tested the sql statement doing the backup to device with the northwind db (because the db i had to backup, was way too large to test it with and i was under the impression that the backup in the device is overwritten, which i now ...more >>

I want to do a very simple thing: I have a column of type datetime, there are dates with a time part, e.g. '2007-05-06 11:25:38', and I want to get rid of the timepart (I want to UPDATE the column), so afterwards the field value is '2007-05-06' (with time 00:00:00). How can this be done with T...more >>

help i seem to have just deleted by mistkae more than 90 perecnt of my emails , and i bneed them back,. how can i undo what i did? help. iam a hjournalist in Taiwna, i need to ask that all the deletaions come back, my acocunt is now 6% fukll, it should be 96 % full....can you restoriemy stuff...more >>

I have two tables, Managers and Employees with the following (partial) structure: Managers LM_PostNo VarChar(10) NOT NULL SummaryDate DateTime NULL Employees LM_PostNo VarChar(10) NOT NULL ...more >>

help i seem to have just deleted by mistkae more than 90 perecnt of my emails , and i bneed them back,. how can i undo what i did? help. iam a hjournalist in Taiwna, i need to ask that all the deletaions come back, my acocunt is now 6% fukll, it should be 96 % full....can you restoriemy stuff...more >>

I have a file storage app which has a table holding the file names of the documents. Unfortunately the delete of the files has bugged and I now have files on the server which don't have a record in the table. How do I generate a new table of all the files currently stored in a directory,...more >>

Hi all, What is the commad to change the property of table or column? Eg: Initially NoOfColumns = 5 What is the script to change NoOfColumns to 7 Thanks Supriya. ...more >>