sql-server-2005

Generate missing dates + Sql Server (SET BASED)

I have the following id eventid startdate enddate 1 1 2009-01-03 2009-01-05 1 2 2009-01-05 2009-01-09 1 3 2009-01-12 2009-01-15 How to generate the missing dates pertaining to every eventid? Edit: The missing gaps are to be find out based on the eventid's. e.g. for eventid 1 the output should be 1/3/2009,1/4/2009,1/5/2...

SQL Server 2005 Data Types and VB.NET

I would like to know how can I use tinyint in SQL Server 2005 with VB.NET. Please let me know if someone know all data types in SQL Server 2005 are matching to which VB.NET Data types. I face that problem now. For example: tinyint in VB.NET, money in VB.NET, smalldatetime in VB.NET. Thanks you all in advance! ...

In Sql Server 2005, how do I setup permissions using schemas and db roles?

Here is my current setup: Database Role - MyDbRole Schema - MySchema User - MyUser MySchema is owned by MyDbRole. MyUser is mapped to my database that contains MyDbRole and MySchema. It is mapped using the MyDbRole database role. I thought that MyUser would now have access to any object within the MySchema schema, because it is owned ...

Does XML AUTO support what I am trying to do?

Hi At present we have a denormalised database. ie Name|NameID|EmployeeID|EmployeeType I'm normalising the database to resolve the EmployeeID from the Employee table rather than the Name Table. So we have a select at the moment SELECT Name, NameID, EmployeeID, EmployeeType FROM Name FOR XML AUTO Which will output: <Name Name...

Multiple Record Update Fire A Trigger Once

Hi everybody, I have a table where on a single record update, a trigger adds a history record in another table. When I run a multiple record update on the table the trigger does not work since it was implemented to work with only one record and a trigger runs only once for a statement regardless if it affects multiple records. Is there...

How do you set permissions on a schema that has objects accessing other schemas?

I have 2 schemas and one of the objects in the first schema needs to access an object in the other schema. For example: CREATE VIEW I.ITest AS SELECT 1 as TestColumn GO CREATE VIEW O.OTest AS SELECT * FROM I.ITest GO EXEC ('SELECT * FROM O.OTest') AS USER = 'TestUser' DROP VIEW O.OTest DROP VIEW I.ITest In the above example...

When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Hi, I just recently learned of the existence of the new "EXCEPT" clause in SQL Server (a bit late, I know...) thru reading code written by a coworker. It truly amazed me! But then I have some questions regarding its usage: when is it recommended to be employed? Is there a difference, performance-wise, between using it versus a correlate...

Lock out non-dbo

Is there an easy way to lock a sql server express 2005 so that only DBOs can get to it, assuming you have a system where everyone has been granted rights individually and you can't just disable a role? ...

SQL 2005 Reporting Services doesn't print properly

We have a 900+ page report that was created in RS. You can only print 1 page at a time from the report manager. We have tried exporting to pdf, etc but the dollar amounts don't line up properly when it is exported. Does anyone have any suggestions to get this to print correctly? ...

Update table without using Cursor

I have data like this I want to update minP, maxP and agvP columns on the basis of Id. My desired result is; Please help. ...

Is the performance of SQL Server cross joins with conditionals in the where statement the same as inner joins with conditionals in the join's on statement?

I am trying to determine the performance of a piece of generated SQL that runs in SQL Server 2005. It uses CROSS JOINS, but the conditionals tying the cross joined tables are in the where statement. I previously thought that all cross joins that have where statements would first pull the full cartesian product and then apply the filter...

How to rollback changes in SQL Server

Hi By mistake I have updated data on production database. Is there any way to rollback those transactions. I have executed the update statement from management studio and the script does not have in Begin Trans/rollback/commit. Thanks ...

SQL Command to execute multiple times?

I have situations that I need to write multiple rows of the same value to setup some tables. Say I have to add 120 rows with two columns populated. I am looking for a shortcut, instead of having the Insert line repeated n times. How to do this? ...

SQL Date function question

I'm using SQL Server 2005 and I have a DateTime column. How can I multiply the hour and minute of a DateTime column by 1.2 and return the new value So something like: SELECT MyColMinute = DATEPART(minute, myCol) * 1.2 MyColHour = DATEPART(second, myCol) * 1.2, MyCol From NyTable Of course the above won't work! So for example myCo...

How to generate a date for the given time?

Using VB.NET and SQL Server 2005 I want to generate a date according to the given Outtime, Am entering the value like ID Date Intime Outtime Holiday 001 23-02-2009 08:00:00 17:00:00 no 001 24-02-2009 17:00:00 08:00:00 no 001 25-02-2009 10:00:00 16:00:00 no 001 26-02-2009 18:00:00 20:00:00 no 001 27-02-2009 yes Ex...

How to repeat the values between the two dates?

Using SQL Server 2005 I want to generate the table values between the two dates Table1 ID Date Intime Outtime 01 12-02-2009 10:00:00 17:00:00 02 13-02-2009 08:00:00 16:00:00 03 14-02-2009 09:00:00 21:00:00 04 15-02-2009 Suppose I want to generate the above table values between the two dates. For Example, Given Date: start date - ...

How to detect and remove a column that contains only null values?

Hi all, In my table table1 there are 6 columns Locations,a,b,c,d,e. Locations [a] [b] [c] [d] [e] [1] 10.00 Null Null 20.00 Null [2] Null 30.00 Null Null Null i need the result like Locations [a] [b] [d] [1] 10.00 Null 20.00 [2] Null 30.00 Null My question is how to detect and delete colum...

How do I convert my CASE WHEN THEN statement?

Please help me to convert the below statement: CASE WHEN TITLE IS NOT NULL THEN 'A' WHEN LOCAL_TITLE IS NOT NULL THEN 'B' END AS COMBINED_TITLE to something like this: CASE WHEN TITLE IS NOT NULL THEN COMBINED_TITLE=TITLE WHEN LOCAL_TITLE IS NOT NULL THEN COMBINED_TITLE=LOCAL_TITLE END AS COMBINED_T...

How to discover trigger's parent schema?

To discover all triggers in any given MS SQL Server database, I'm currently querying the sysobjects table (which is fine because it works in MS SQL Server 2000 which I have to support) e.g. SELECT R1.name AS trigger_name, T1.name AS trigger_parent_table_name FROM sysobjects AS R1 INNER join sysobjects AS T1 ...

Consolidated: SQL 2005+ Dynamic Pagination using SET ROWCOUNT or RowNumber() ?

I've a complex SP which applies multiple JOINs and lookup and come complex filters like comma-separated values, etc... On top of it, I've to deploy two complex yet performance-effective features: 1. Dynamic sorting but I see its limited - you kno the long/clumsy CASE hierarchy, its strange that experts also agree that this is the only '...