Trunc(sysdate) in SQL Server
What is the equivalent of: TRUNC(SYSDATE) ...in SQL Server 2005? ...
tsql
SQL Server Stored Procedure Fails due to use of XML/ ANSI_NULLS, QUOTED_IDENTIFIER options
I have a stored procedure which takes an XML parameter and inserts the data into multiple tables. If I run the stored procedure into a database using a SSMS query window, everything works fine. However, we have a custom installation program that is used to deploy stored procedures to databases, and when this is used, execution of the sp ...
T-SQL - Creating a Local User On Windows OS
Hi, My requirement is that I need to create a local user on my System (On my Windows OS) using T-SQL. And I need to set this user under ‘Administrators’ group. Using this local user I should be able to login to my Windows OS (At System Startup). Is it possible? If so may I know how could we do this? Many Thanks, Regards. Anusha. ...
Inserting Randomly Selected Records SQL
I'm using Microsoft SQL Server 2005. I'm creating a random record generator that will insert 10 records randomly into a temporary table. The records in the temporary table will then be used to update records in the table in memory. Here is the statement that is giving me some troubles (assume the temp table is already created). i...
SQL Server add a column constraint to limit data to -1 to 1
Hello, I want to constrain a SQL Server decimal column to only allow -1,0,1 as valid values. Can you show me the SQL syntax for adding such a constraint. (I would like to know how to do it in both the CREATE TABLE statement and/or the ALTER TABLE ADD CONSTRAINT). Or can this only be accomplished in a trigger? Seth ...
Find out the values between a range in SQL Server 2005(SET BASED APPROACH)?
I have a table like Id Value 1 Start 2 Normal 3 End 4 Normal 5 Start 6 Normal 7 Normal 8 End 9 Normal I have to bring the output like id Value 1 Start 2 Normal 3 End 5 Start 6 Normal 7 Normal 8 End i.e. the records between Start & End. Records with id's 4 & 9 are outside the Start & End henceforth...
tsql casting to money rounds up
When casting a varchar value to MONEY it is rounding the value to the nearest 0.10, how do I prevent this rounding up? UPDATE: I found the problem. In a subquery, the value is being CAST from varchar to FLOAT and then I was trying to CAST from FLOAT to MONEY. ...
Before trigger in SQL Server
I have 2 tables: survey (id(PK), name) and survey_to_topic (survey_id(PK,FK,not null), topic_id(PK,FK,not null)). When I try to delete from survey table, I get exception: "The DELETE statement conflicted with the REFERENCE constraint "FK_survey _to _topic _survey". The conflict occurred in database "mydatabase", table "dbo...
T-SQL DENY EXECUTE
hi what is the t-sql command/syntax to deny execute permissions to EVERYONE except the dbo on stored procedures? I want to grant execute to roles and users will then inherit rights based on the roles they belong to. as such i want new users to be denied execute on all the stored procedures i create. thanks sorry - i should mention i d...
Crystal Report SQL Statement Generation for MS SQL
If you create a Crystal Report using the built in wizard, tables are linked, etc. What determines whether a parameter makes it into the actual SQL statement? Also, why does it enclose everything in double quotes? For example: Here would be my base SQL Statement as generated by CR: SELECT "poitem"."fpono" , "pomast"."fcompany" ...
Help with ORDERING ROW_NUMBER OVER the Count in Descending Order?
I am using the ROW_NUMBER() function introduced in SQL SERVER 2005 to return a paged set of results. The Query Works as expected, but I have one issue. What I would like to do is return the results ordered by the count in descending order. Here is the query and below I will give a little description: DECLARE @StartRowIndex INT DECLAR...
How to add variables in Dynamic Sql, not concatenate them?
I have the following dynamic sql statement where I want to add @StartRowIndex + @MaximumRows and subtract 1 from it. I am unclear on where to put the single quotes in the statement. Here it is: SET @sql = @sql + ' SELECT * FROM LicenseInfo WHERE RowNum BETWEEN ' + @StartRowIndex + ' AND ' + '(' + @StartRowIndex + @MaximumRows + ')'...
Users need to create dynamic rules that apply to other users access to the group
Okay so here is "in a nutshell" what I'm trying to accomplish... Users of my application can go and create a new group. They can specify criteria about other users which they will allow/deny to determine who is allowed to join the group. Example: Age: 12 - 16 yrs old Height: 5 - 6 feet The data table that stores the rules would be li...
TSQL: Cannot perform an aggregate function AVG on COUNT(*) to find busiest hours of day
Consider a SQL Server table that holds log data. The important parts are: CREATE TABLE [dbo].[CustomerLog]( [ID] [int] IDENTITY(1,1) NOT NULL, [CustID] [int] NOT NULL, [VisitDate] [datetime] NOT NULL, CONSTRAINT [PK_CustomerLog] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY] The query here is around finding the distributi...
TSQL Cascade delete for child records?
I have a parent/child table (simple tree) table structure (ID, ParentID), where I want to delete (and get the ID of) all children for a given parent ID - similar to this post http://stackoverflow.com/questions/1433808/sql-server-cascade-delete-and-parent-child-table . During the loop, where I've got the current ID, I will also be perfor...
Parameterizing Dynamic SQL issue with SQL_VARIANT datatype.
I've been refactoring a huge dynamic SQL statement, and think I may have hit a roadblock. The goal has been to parameterize it. I'm using SQL Server 2005. I have an int variable (@i) that determines what column I need to update. Here's what I've been doing: if @i = 1 begin set @updateClause = 'Column1 = @newValue'; set @updateClau...
Remove and split data into multiple columns in select statement.
I have data like following table I want to remove Titles ( Mr. Miss, Dr etc) from name and want to split data into First name and last name if two names exists. I want this in select statement. I can remove title using CASE statement but unable to split name into tow in same case statement. I want data like this but in select statem...
[TSQL] Parent-Child view from table that is self referencing (ID)? (for tsql gurus.)
I have a organization name table with the following structure given below: CREATE TABLE [dbo].[DP_ORG_OrganizationUnit]( [GID] [uniqueidentifier] NULL, [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Code] [nvarchar](100) NULL, [Name] [nvarchar](100) NULL, [LastUpdated] [datetime] NULL, [ManagedBy] [int] ...
How to refresh the definition of a T-SQL user-defined function after a dependent object is redefined?
Consider the following sequence of events: A view v_Foo is defined A user-defined function GetFoo() is defined that includes all columns from v_Foo (using 'Select * ...') The definition of v_Foo changes and now includes more columns I now want GetFoo() to include the new columns in v_Foo, but it still references the old definition I ...
Customer and Order Sql Statement
TSQL query to select all records from Customer that has an Order and also select all records from customer that does not have an Order. The table Customer contains a primary key of CustomerID. The table Order contains a primary key of OrderID and a foreign key of CustomerID. ...