sql-server

get date of all saturdays in a given year - sql server

I need to get a list of all Saturday dates in a given year. I've seen an oracle post that goes against a table that had "fiscal calendar table" but I haven't been able to succeed in converting it nor do I have a table that contains a set of dates I want to investigate. SELECT DATE DATES,TO_CHAR(DATE,'DAY') DAYS FROM FISCAL_CALENDAR WHE...

Rules are deprecated, what's instead (TSQL)?

Rules (Transact-SQL)[1] are reusable what permitted to overcome the shortcoming of non-re-usability of check constraints. And now I read [1] that: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feat...

row with minimum value of a column

Having this selection: id IDSLOT N_UM ------------------------ 1 1 6 2 6 2 3 2 1 4 4 1 5 5 1 6 8 1 7 3 1 8 7 1 9 9 1 10 10 0 I would like to get the row (only one) which has the minimun value of N_UM, in this case the row with id=10 (10 0). ...

Convert varchar colum to smalldatetime

I have a table with 800+ records. In this table I have a column named 'Data' of varchar (10) datatype which contains dates in 'dd.MM.yyyy' format.I want to convert it to smalldatetime. I've tried converting it using Enterprise Management Studio Express, but I receive this error: 'The conversion of char data type to smalldatetime data t...

Daily SQL Task to delete contents from a table

Hey I was wondering how I could set up a clean up task on a particular table to delete contents thats lets say a week old. I am using SQL Server 2005 ...

nested set and treeview control

Hi All, Given the following database tables and sample data: Locations Id ParentId LeftIndex RightIndex Description -- -------- --------- ---------- ----------- 34 2 85 104 Florida Region 73 34 94 95 Miami Products Id ParentLocationId Code Description -- ---------------- -...

Getting result of dynamic SQL into a variable

Hi, Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city How do I use the count(*) column value as return valu...

I've tried EVERYTHING! How can I stop: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

So I'm trying to make a simple C# console app that simply queries a database - nothing more. However, I keep getting this error: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: A network-related or instance-specific error occurred while establishing...

Do stored procedures lock tables/rows?

Quite a simple question. In SQL 2008 if I have a stored procedure (see below) do I run the risk of a race condition between the first two statements or does the stored procedure put a lock on the things it touches like transactions do? ALTER PROCEDURE [dbo].[usp_SetAssignedTo] -- Add the parameters for the stored procedure here ...

script to add and remove auto-increment property from a column

For a sql script I'm working on, I need to programmatically remove the identity, identity seed, and identity increment for a column in an existing table, then add them back to the table at the end of the script. Does anyone have a reference or an example on how to do this? ...

c# DateTime, Trim without converting to string

How can I "trim" a value of a DateTime property in c#? For example, when I get a date it is of the format "10/1/2010 00:00:00". How can I "trim" 'Time' 00:00:00 without converting this to a String? Since I use a property of type DateTime to manipulate this, I don't need to convert to String. Any help is appreciated. ...

How to duplicate an entire column with values with Microsoft SQL Server?

I have a column in a table of +15000 entries - I want to duplicate that entire column while retaining it's values and name it differently. I'm using Microsoft SQL Server Management Studio. How do I go about that? Thanks. ...

Checking for invalid date in SQL Server Stored Procedure

I have a stored procedure in SQL Server 2008 that will insert a record into a table. The sproc looks similar to this: -- params @f1 int, @f2 datetime = null, @f3 datetime, @f4 bit = 0 BEGIN INSERT INTO dbo.table (fields......) VALUES (@params.....) RETURN @@IDENTITY END Prior to running the INSERT stateme...

Online interpreter for SQL Server T-SQL code?

Is there any possibility to test T-SQL code on the internet? I mean that e.g many programming languages like Python has several online shell interpreters on the internet. Is there one for T-SQL? ...

Sql Server Indexes Include Primary Key?

One of my co workers is under the impression that when adding an index to a table in SQL Server 2008 that the PK's index is added to that index as well. Therefore if you are using a wider primary key then that key will also be included in the new index vastly increasing the disk space used above and beyond the penalty already paid for th...

select column name from max query

I have a query that goes something like this : ;WITH t as ( select 1 as RowNumber, 1 as ObjectID, 10 as [Col1], 20 as [Col2], 20 as [Col3], 20 as [Col4] UNION ALL select 2 as RowNumber, 2 as ObjectID, 20 as [Col1], 30 as [Col2], 40 as [Col3], 50 as [Col4] ) SELECT RowNumber, ObjectID, ( SELECT MAX(Amount) ...

UPDATE with a stored procedure on SQL Server 2005

Hello. I want to say UPDATE users SET field = my_sp() in SQL Server 2005. Apparently I can't do this and have to use some form of EXEC. Can anyone help me out and let me know how to do this? This should be some easy rep. ...

How do I build a Resource Governor classifier function based off of a database role?

I am trying to write a classifier function for the SQL 2008 resource governor. I would like to use a user created database role to identify if the user should go into a particular work load group. The logins in question are SQL logins. I can not use IS_MEMBER(), because IS_MEMBER restricts itself to the current database context (in th...

Where does a Server trigger save in SQL Server ?

Hi , A couple of days ago , I was practicing and I wrote some triggers like these : create trigger trg_preventionDrop on all server for drop_database as print 'Can not Drop Database' rollback tran go create trigger trg_preventDeleteTable on database for drop_table as print 'you can not delete any table' rollback tran But the pr...

T-SQL IF condition is not working as expected

I have a stored procedure that will INSERT a record into a table. I have created a unique key constraint on the table to avoid duplicate records. Regardless of the fact that there is a unique key constraint defined, I am using an IF() condition (prior to the INSERT statement) to check for a duplicate record that may already exist. How...