sql-server-2005

SQL Server database keeps locking

I have a SQL server 2005 database that keeps locking and won't release. My application cannot commit updates to the database because there are tasks that are waiting to be processed and my app keeps crashing. If I look on activity monitor, waiting tasks just keeps going up and up until I kill the process. The problem is I can see what...

MS Sql Server table based user authorization

Hi there, I want to restrict some MS Sql Server users. For example, one user should just be able to see 2 columns of "Customers" table and none other. And this user shouldn't be able to create any manipulation queries. But one user should be able to do everything on all tables and all columns. How do i do that? BR, Çağın ...

SSIS packages to import Excel Files into SQL Server

We have a number of import jobs that previously worked fine. These dtx packages were execute from VS2005 on a Windows XP box with Office 2007 installed. The developer has not got a nice new machine, 64bit, Windows 7, Office 2010. Now when he tries to execute the packages he gets the following error: SSIS package "Import_BygningsKa...

Suitable indexes for sorting in ranking functions

I have a table which keeps parent-child-relations between items. Those can be changed over time, and it is necessary to keep a complete history so that I can query how the relations were at any time. The table is something like this (I removed some columns and the primary key etc. to reduce noise): CREATE TABLE [tblRelation]( [dtCr...

The multi-part identifier "Reservation.ReservationID" could not be bound.

I am trying an inner join like this. select R.ReservationID, R.BookingNumber,P.FirstName, P.LastName from Reservation R inner join PersonName P on Reservation.ReservationID = PersonName.ResrvationID I am getting the errors as: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Reservation.ReservationID" could not be b...

Change default db to Master db in SQL Server

I have deleted my default db now I can not login using sql server authentication. How can I change default db back to Master db EDIT: I cannot login using sql server authentication. It gives me error Cannot open user default database. Login failed. Login failed for user 'xyz'. (Microsoft SQL Server, Error: 4064) Thanks. ...

How to tell if a variable (-v) is defined on command line (SQLCMD)

Is there a way to tell if a variable is defined on command line using SQLCMD? Here is my command line: sqlcmd -vDB="EduC_E100" -i"Z:\SQL Common\Admin\ProdToTest_DB.sql" Inside ProdToTest_DB.sql I would like to set some sort of conditional IF to check if the variable does not exists and define it if it does not. IF NOT $(DB) :set...

How to transpose rows into columns?

My database allows User Defined Fields(UDF) and during select, I want to combine the regular table fields with the UDFs in another table. Here is an example. Table Person Id FirstName LastName 1 John Smith 2 Jim Adams Table UDF PersonId FieldName FieldValue 2 Age 28 2 Status Available R...

SQL Server 2005 Get First and Last date for any Month in any Year.

I have a stored procedure that has to accept a month as int (1-12) and a year as int. Given those two values, I have to determine the date range of that month. So I need a datetime variable to represent the first day of that month, and another datetime variable to represent the last day of that month. Is there a fairly easy way to get th...

Adding a type of heading to a query

Hey all, im not sure if this is possible but i am looking to add a heading to a query's output. The best way to describe this is just by showing what i would like the output to look like: [heading here] <skip row> [Another Header here] MemebersID | FirstName | LastName | Email | OrderDate 05466 Bob Barker ...

SQL Server recursive query

Hello, I am new to SQL Server development. Most of my experience has been done with Oracle. suppose I have the following table that contains Appointments objects CREATE TABLE [dbo].[Appointments]( [AppointmentID] [int] IDENTITY(1,1) NOT NULL, ....... [AppointmentDate] [datetime] NOT NULL, [PersonID] [int] NOT NULL, ...

IN NOT IN SQL Server 2005

How can I see what is not in the table... I know I know...can only see what is there but come on!!! So!! select * from ORDER where State IN ('MA','PA','GA','NC') So I will get MA and PA but I want to see GA and NC.... NOT IN will return NY,NJ,CT ect.... I just want to see what is in the ( ) ...

i get an error when creating a variable and specifing the collation in sql server 2005

when i tried this: DECLARE @var nvarchar(500) collate Arabic_BIN i got that: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'collate'. that is the full code, it works, i do not know how but the person who give it to me have used it successfully CREATE FUNCTION fn_RemoveTashkeel (@InputString nvarchar(2300) ) ...

Insert into from temp table - creating too many rows

I am bulk importing CSV file into a temporary table and then inserting the rows into the target table if they do not already exists. When I start the process, my table has the following values VECH01 AAA 111 VECH01 BBB 222 VECH01 CCC 333 VECH02 AAA 111 VECH02 BBB 222 VECH02 CCC 333 When I import a CSV file with these values: VECH01...

Hidden dynamic matrix groups still take space in SSRS

The Problem I am using dynamic matrix groups as in Dynamic Grouping from Chris Hays's Reporting Services Sleazy Hacks Weblog (which has some great stuff and is worth checking out, by the way). I have my row and column groups bound to two multi-value parameters and everything is working great, except for one thing. Even though I am suppr...

Data Migration : What way is the best way to Migrate?

hi All, I am doing data migration two database in MS SQL 2005. The records are more than 200000 nearly 300000. I need to query from source database using joins and insert 2 or 3 tables in destination databse. Which way is the best way to do so? Please suggest me. I am trying to write a program with VB.NET. If you have sample, please sh...

The tablix 'Tablix1' is invalid. The value for the DataSetName Property is missing - Help

For some weird reason i can not run my report, when i try to preview i get this error: (PLEASE RIGHT CLICK THE IMAGE AND CHOOSE VIEW IMAGE TO SEE IT LARGE) Here is how my tablix property looks like: Any idea what to do? The current .rdl was copied from another one. I changed the newly copied .rdl values to match my needs. I always...

Using a table as a queue of work for multiple processes in SQL 2005

Given a table (JobTable) that has 2 columns: JobId, JobStatus (there are others but I'm excluding them as they have no impact on the question). A process, the WorkGenerator, INSERTs rows into the table. Another process, the Worker, executes a stored procedure called GetNextJob. Right now, GetNextJob does a SELECT to find the next pie...

SQL field = other field minus an other ROW!

Table has 2 cols: [nr] and [diff] diff is empty (so far - need to fill) nr has numbers: 1 2 45 677 43523452 on the diff column i need to add the differences between pairs 1 | 0 2 | 1 45 | 43 677 | 632 43523452 | 43522775 so basically something like : update tbl set diff = @nr - @nrold where nr = @nr but i don't want...

SQL stored procedure Join question

Hi, currently the stored proc returns one record based on a employee_number that is passed as a parameter. If the surpervisor_id is NULL, there is no record returned. If I want to return a record with all the other fields, and have a NULL in the supervisor_id field, how would I configure that? SELECT DISTINCT tblPeople.PERSON_ID,...