I got the following, DECLARE @temp TABLE( rownum int identity(1, 1), Usr int, Val int ) INSERT INTO @temp( Usr, Val ) SELECT Usr, Val FROM D ORDER BY Usr, Val SELECT A.Usr, AVG(1.0*A.Val) AS medianValue FROM @temp A INNER JOIN ( SELECT Usr, FLOOR(AVG(1.0*rownum)) AS m1, CEILING(AV...more >>

I have a database user (let's call the user TestUser) and I am needing to script the granting of role db_datareader to TestUser. BOL is rather cryptic on how this is to be done, providing the following syntax: GRANT permission [ ,...n ] ON { [ USER :: database_user ] ...more >>

Hello, I have this sproc to insert csv files in my tables. First, It truncates the table. Afther that, it inserts the csv file. CREATE PROCEDURE [dbo].[I_TDE_Imports] @FilePath VARCHAR(500), @FileName VARCHAR(40), @TableName VARCHAR(6) AS DECLARE @sql NVARCHAR(2000) DECLARE @Delete ...more >>

Running SQL Server 2005, SP1 on Win2K3 with 8GB RAM (6GB to SQL Server and 2 GB to the OS). Just recently a server started exhibiting high cpu. I ran profiler and identified serveral stored procedures that can use some tuning. I also analyzed blocking, and there was very little blocking goin...more >>

Ok here is the deal. I have a "Live" table that stores 11 rows of data with several columns. About every 10 seconds two values change on this table (a column called value which stores a number and a timestamp). I have a procedure that takes these rows and inserts them into another table. I w...more >>

HI all, I need to combine a varchar(200) column from two rows into one row. The rows are the result of a query, and are unique in accountId, Year, and month, columns. I thought of a pivot, but because the varchar column would differ so much of the table, that would seem inpractical. I trie...more >>

Hi all, I am trying to speed up performance in a heavily used database. The database is accessed via a VB front-end that I wrote, so I have a pretty good idea what's going on with the 50 or so users. There are usually about 1000 maximum updates and/or inserts of invoice records done daily....more >>

Is the geospatial in the July CTP? If not where can I find info on it?...more >>

I need another set of eyes on this. I am missing something and can't figure this one out. I have a table (definition follows) and when I run the query (definition in the post) I get duplicate rows. I have included the data for the table that is giving me problems as well (CSV). Can anyone s...more >>

I have something like this in my Access databse.. ([LOANPROGRAM] <> 'CONSTRUCTION') AND ([PROPTYPE] IN (2010, 2020, 2030, 2040, 2050)) And I need to use a Table Update in SQL to update my table. Am I close or way off UPDATE dbo.pplCheckListItems SET LoanCriteria = '([loanProgram] <> 'Co...more >>

I have Sql 2005 Standard edition. I have a folder for snapshots available but cannot create them does anyone know if this will be supported in a future service pack for standard edition or is it there as a constant reminder of " look what you could have got if you had got Enterprise Edition."...more >>

Hi, I'm pretty weak at SQL. I'm trying to group the count of rows by the value of one of the columns. I tried this: SELECT Count(*), CASE WHEN perspvalue=0 THEN 0 WHEN perspvalue<1000 THEN 1000 WHEN perspvalue=5000 THEN 5000 WHEN perspvalue=10000 THEN 10000 ELSE 9...more >>

Hi, I'm not sure if my following question should be posted in this discussion group or another... but here it goes: We had recently migrated our vendor's SQL 2000 DTS packages to SQL 2005. One of these package uses the extended system SP, xp_cmdshell to create a directory. The problem...more >>

I need to take all databases off an existing sql 2000 box and move them to an existing 2005 box. Is there an easy way I can move all databases and logins, etc.? I don't really want to have to recreate all logins, etc. Thanks a lot. ...more >>

I have Organization table linked to and Event table on PK named Org_ID int (identity). The Organization table is the parent of the Event table (1 to many). The Organization table has the following relevant columns: Org_ID (PK) int ParentOrg_ID int (can be null) OrgName varchar(50) OrgType ...more >>

I am creating a table to store data that is exported from an ACD phone system. Many of the columns are data that is in Minutes:Seconds format. I will need to be able to do aggregations on these columns (i.e. Avg Talk time per user). What is the recommended data type for storing and working wit...more >>

Hi, I'm running SQL Sever 2000 and am having trouble figuring out how to write this query. We have a table, abreviated to this: CREATE TABLE #temp (claim varchar(5), adj_code varchar(15), amount decimal (14,2)) INSERT INTO #temp VALUES ('00001', '1', 1.5) INSERT INTO #temp VA...more >>

Hello, I have a trigger which inserts a new record into the database and updates various tables. It is used to add teachers to a school record. It will automatically create the new record, insert the school's address into the teacher record and create an email address on the teacher record ...more >>

I'm trying to reset an Identity column - but my DBCC statement doesn't = like me having my schema name before the table name: DBCC CHECKIDENT (determination.EvidenceGroupDim, RESEED, 1) Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'....more >>

Why the perfomence is so different? I have a huge table. I try to update some records, but using different way spend huge different time. Update used four hours. Insert into used 40 minutes. And select into just used 10 minutes. I can understand update need more time because matching record spen...more >>

Hi I have a java application that connects to SQL Server 2005 as backend . There are certain records that are updated by the application, which I need to lock as they are being updated. I wonder if someone could explain if they should be done inside Java code or SQL Server 2005 level ? ...more >>

Our team provide different types of services such as: - proffessional cracking of any kind of software (CAD,CAM,CAE,EDA,GIS,PCB,FEA,FEM,CNC,CFD,PDS,3D,Optics etc.) designed for any kind of operating systems(Windows 95/98/ME/2000/XP, Linux, FreeBSD, OS/2, MAC OS etc.) - producing key...more >>

What is the best way to track blocked/blocking processes in SQL 2005? I don't want to use Profiler because it needs to be running all the time. Thank you in advance for your help, Leon...more >>

I am trying to create/use a Multistatement Table-Valued Function but I'm having a hard time wrapping my head around this. I've searched but can't seem to find the answers, so I have a feeling that either it's something really obvious or I'm taking a totally wrong approach. Here's what I'm try...more >>

I am trying to create an installation package for a BI solution which uses custom SSIS components. I would like to add the custom SSIS components to the "Data Flow Transformations" tab page of the toolbox of the Business Intelligence Development Studio from the installer. I have tried to accompl...more >>

I tried to use partition scheme on my tables in database, but have one problem. I used one column to divide data to two partitions. This column had to be added to primary key columns, which I understand. But i need to have constraints referenced to my origin primary key columns. I tried to creat...more >>

Is there a way to convert a 2005 database to 2000? We have a document system that uses 2005, but we still have lots of customers who're using 2000. ...more >>

Hi, I have a database that is pretty large in size (almost 200GB) I need to do an Insert statement that selects data from another table straight forward INSERT INTO Table1 SELECT * FROM Table2 Table2 is huge in size, the time needed to complete the INSERT statement is 6-7 hours. The rec...more >>

hi, i want to migrate data from MSSQL to Oracle, data transfer successfully but i could not open that table in oracle. an error occurs ' table or view does not exists'. If any buddy know how to convert data after migration data then please let me know . thanks in anticipation. ...more >>

hi all . adv thanks...more >>

Hey I got a problem. I have a new serverinstallation and I'm trying to create a job. But I get this error (which I've never encountered on other 2k5 installs) "Cannot start the job "Start Incremental Catalog Population on Testjob" (ID 541CE21B-578B-475A-A526-C7AE75FD29E4) because it does no...more >>

currently experimenting with IN and vars e.g. IN(@myvar) - this works ok, i can pass a variable and it works great, however how can i pass it more than one value ? e.g. i can pass it say 'apple' and itll produce apples, but how can i pass it say 'apple','pears' cheers mark ...more >>