sql-server-2005

How to be guaranteed transactional integrity in SQL Server 2005

I have what looked to me at first glance a very simple problem. I want to be able to obtain a unique key value with a prefix. I have a table which contains 'Prefix' and 'Next_Value' columns. So you'd think you just start a transaction, get the next value from this table, increment the next value in the table and commit, concatenate the ...

SQL: Select As Columns

Hi I have a table with data in it as such: Cars: id | name ---------- 1 | Buick 2 | Honda 3 | Toyota What I would like to do in a Stored Proecdure is get the result like this: Temp Table: Buick | Honda | Toyota ---------------------- I realize it has no values, but I just want to get this part first. I am guessing this would in...

Data Paging in SQL Server 2005 Only Returns 1 (One) Row

Hi, Would someone please help me. I have a stored procedure (see below) for data paging in SQL Server 2005. The purpose of the stored procedure is to return a page of Products (prodid, description) for a particular Category (CatID). When I execute the stored procedure: EXEC @return_value = [dbo].[ecosys_CNET_GetCategoryProducts] @Ca...

Problems executing a SSIS package deployed to the file system

I have a SSIS master package, which executes several child packages. It works great, but when I deploy it to the file system on the server, I get an error code "0xC00220DE". "The system cannot find the file specified." When I run the package on the server by double-clicking it, it works correctly. But when I use DTExec: dtexec /FILE "d...

How can I easily flatten this Sql Server heirarchy into an inherited inclusive list?

I have tables (for simplicity sake) as outlined below: Category -------------------- CategoryId (0 for root nodes) ParentCategoryId ProductCategory -------------------- ProductId CategoryId I'd like to be able to retrieve a distinct list of all categories and their inherited products (all the way back to category 0). Category 0 shou...

SQL Server how to drop identity from a column

Is there an easy way to remove an identity from a table in SQL Server 2005? When I use Management Studio, it generates a script that creates a mirror table without the identity, copies the data, drops the table, then renames the mirror table, etc. This script has 5231 lines in it because this table/column have many FK relations. I'...

SQL Server Logging/User Tracking Capabilities

We have recently converted an Access Application to store the date on SQL server. The forms still reside in the MS Access, but are linked through file DSN's to SQL server. A question came up of the capabilities to track user activity within SQL server. I have speculated that if we set up individual users in SQL server and use these ind...

How do I expand comma separated values into separate rows using SQL Server 2005?

I have a table that looks like this: ProductId, Color "1", "red, blue, green" "2", null "3", "purple, green" And I want to expand it to this: ProductId, Color 1, red 1, blue 1, green 2, null 3, purple 3, green Whats the easiest way to accomplish this? Is it possible without a loop in a proc? ...

SQL Server 2000-2005-2008 Distributed Query Problem

Hello, Just set up a new server. Trying to transfer some workload from one to the other. Everything looks great, except when I come to run this code: CREATE TABLE #KEYWORD5 (ITEM_MASTER_ID NUMERIC(25) NULL) INSERT INTO #KEYWORD5 exec SQL2K801.soupftidx.dbo.P_REMOTE_SQL_EXEC 'SELECT IM.ITEM_MASTER_ID FROM ITEM_MASTER IM WH...

Adding a new dimension based on a key in fact table linked to one of the dimension tables

Hi, I have a fact table that holds all date & time attributes as keys which links to actual DATE & TIME dimension. When I create a cube on top of it using SSAS 2005, these datetime attributes are split into individual dimensions for the CUBE, which is OK. Problem is when I add a new datetime attribute to the fact table, my cube doesn't ...

SQL Server - An error occurred while executing batch. Error message is: The directory name is invalid.

Our database server had run out of disk space, after freeing up some disk space any query run in sql server management studio, with the results sent to grid view, resulted in this error: An error occurred while executing batch. Error message is: The directory name is invalid. When the results sent to text view the queries worked fine. ...

how to get alert when particular job is executed in SQLSERVER

Hi i have created a job that contain the command "select * from employ" from user databse. when this query executed succesfully i hav to get alert by mail.how to do this ...

What happens when I drop a clustered primary key in SQL 2005

I've a PK constraint - a clustered index on two columns - which I am in the process of dropping. The command is still running after an hour. I would have thought that as I am just removing a constraint the operation would be nearly instantaneous. Can someone explain to me what is actually happening under the hood when I drop the PK? ...

SQL Reporting: Matrix Columns Merge

Hi I have data being returned and I put it in a matrix. I have my rows set and my columns set. I only have one column entitled "GTA" and a few rows. My data is coming out like this for some reason. GTA GTA GTA Answer ======================== 1 1 1 2 1 1 1 ...

Best way to change clustered index (PK) in SQL 2005

I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows: ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] I want to remove this clustered index PK an...

Get schema of proc's select output

I'd like to put the results of a stored proc into a temp table. It seems that the temp table must be defined beforehand and an INSERT INTO will not work. Anyone know how to get the schema of the recordset being returned from a select statement? sp_help only gets info on parameters. ...

SQL Server 2005 CE 3.5 Re seed IDENTITY

I have inserted some rows into a data table with Set Identity_insert tblEvent on I then attempt to 'reseed' the Identity field int MaxId = this.MaxID()+1; string upgrade = "ALTER TABLE " + Table + " ALTER COLUMN ID IDENTITY("+ MaxId.ToString() +",1)"; System.Data.SqlServerCe.SqlCeCommand cmd = new System.Data.SqlServerCe.SqlCeCommand(...

SQL 'ORDER BY' slowness

Is it true that ORDER BY is generally pretty slow? I am trying to run some sql statements where the WHERE clause is pretty simple, but then I am trying an 'ORDER BY' on a VARCHAR(50) indexed column. I need to sort alphabetically for display reasons. I figured that getting the database to do it for me is the most efficient. At this poi...

How would a SQL Server 2005 database lose a few days data?

Hi! I really need some help here. I'm the owner of a SQL Server Database application that lost three days data! I can't understand how or why. So here is the set-up. SQL Server 2005 32bit standard edition database on Windows 2000 server. (Database B) Database is in simple recovery mode The database is connected as a subscriber to ...

Create NEW database from Snapshot (SQL Server 2005)

I'm using SQL Server 2005. I've created a snapshot of database (RM_DB), I want to create (RM_DB_NEW) from that snapshot. The only option i get when I right-click on my snapshot is "Restore [RM_DB] from this snapshot". Can I create a NEW database from my snapshot? EDIT I'm not interested in creating a backup out of the snapshot, this w...