tsql

TSQL Default Minimum DateTime

Using Transact SQL is there a way to specify a default datetime on a column (in the create table statement) such that the datetime is the minimum possible value for datetime values? create table atable ( atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Modified datetime DEFAULT XXXXX?????? ) Perhaps I should just leave it null....

What is the equivalent of 'CREATE TABLE ... LIKE ..." in MSSQL

I am working with MSSQL (I am a MSSQL noob) and trying to alter a table. I want to CREATE TABLE LIKE to safely store data while I drop keys and constraints and all the other rigamorole that MSSQL seems to require when altering on the original table but I have not been able to find a match to that command... ...

Passing an "in" list via stored procedure...

Duplicate of: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767 How can I construct a stored procedure that will allow me to pass (for example) an @IDList so that I can write: Select * from Foo Where ID in @IDList Is this doable? TIA! Kevin ...

SPF compliant message with "reply-to" header inside of T-SQL

I've got the following T-SQL code below that's working from inside of SQL Server 2005. I'm looking to add a "reply-to" header so that any reply will go to the "reply-to" address, NOT the FROM address. Yes, I know about setting this up in Database Mail, but I'm looking to just add some code to my existing solution. I don't want to use ...

What is the best way to store a money value in the database?

I need to store a couple of money related fields in the database but I'm not sure which data type to use between money and decimal. ...

SQL Optimization

Is there a performance difference between these two queries. Apparently WHERE should be reserved for querying values on the table to be SQL 92 compliant, is there a performance reason? SELECT Foo.FooId ( SELECT Bar.BarId FROM Bar WHERE Bar.FooId = Foo.FooId FOR XML PATH('Bar'), TYPE ) As 'Bar' ...

Need help with a query (using joins)

I have a three tables: Checkbook table holds what I need to get Receipts table holds a link to a owner table Owner table knows what checkbook it is linked to I need to get the checkbooks only when there are rows in the receipts that are linked to it (through the Owner table). I don't know exactly how to do this, and it kinda seems ci...

SQL scripts act on master database instead of practice database

I wrote some sql scripts to create a database and store data. I just noticed that the new tables and data are going to the master database. I found that I can address the correct database if I scope out the database as so: CREATE TABLE Practice1.dbo.Experiments ( ID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar ...

What are good resources for learning basic T-SQL for non-programmers?

Some of our clients want to do their own reports and lack the necessary knowledge. What are good free resources for learning basic T-SQL? What resources do you recommend to your clients and others wanting to start with SQL? Update: I totally agree with Cruachan, it's better not to do it. We usually don't go down that road and know ve...

how do I backup logins and jobs from SQL server 2005?

Is there a script or function in sqlserver2005 that i can backup the jobs and login details of a server from the master database? ...

T-SQL GROUP BY: Best way to include other grouped columns

I'm a MySQL user who is trying to port some things over to MS SQL Server. I'm joining a couple of tables, and aggregating some of the columns via GROUP BY. A simple example would be employees and projects: select empID, fname, lname, title, dept, count(projectID) from employees E left join projects P on E.empID = P.projLeader group by...

SQL to return list of years since a specific year

I need a list of years as a recordset starting with 2004 to current year (in desc order), without writing a stored procedure. Is this possible? (SQL Server 2005). So it should return: 2009 2008 2007 2006 2005 2004 ...

SQL HELP - Conditional where clause based on a BIT variable - SQL Server

I need help writing a conditional where clause. here is my situation: I have a bit value that determines what rows to return in a select statement. If the value is true, I need to return rows where the import_id column is not null, if false, then I want the rows where the import_id column is null. My attempt at such a query (below) doe...

How do you change the datatype of a column in MS SQL?

I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table? ...

tsql : how do I join this table?

I have the following sql statement: SELECT TOP (100) PERCENT inv.CompanyID, cust.CustID AS ClientID, cust.CustName AS CustomerName, inv.InvcKey, inv.PrimarySperKey AS SperKey, inv.TranID AS InvoiceNumber, in...

Formatting Timespans within Business Objects Desktop Intelligence (BO Deski)

Can anyone tell me how to format a given number of seconds (for example 16742 seconds) in a human readable form (for example 1d 5h 2m 13s) using a BO field formula or from within a T-SQL SELECT statement (where the Duration as Seconds is from a SUM aggregate)? ...

MS-SQL: Convert all instances of one column type to another?

I have an SQL Server 2008 database, which contains about 40 tables. Most of them contain at least a couple of varchar columns, and I want to convert every single one of those to nvarchar (of the same length). Also want to convert a number of text fields to nvarchar(MAX). Is there a simple, straightforward way to do this? ...

MS TSQL for xml path question regarding xpath element

Hi there, I have the following TSQL statement: select tblName "TblName", structure "TblName/STRUCTURE", sqlRetrieve "TblName/SQLRETRIEVE", Identifier "TblName/IDENTIFIER", '2' "TblName/OBJECTTYPE" from configTable for xml path ('') which outputs: <TblName>PD_CODE_PRODUCTS <STRUCTURE>PD_CODE_PRODUCTS</...

MSSQL - Split a field into 3 fields

I have a resultset consisting of 1 column and in this case 2 rows the single column [ProductDescription] is a varchar field that hold 3 pieces of information (I didn't design it) i need to get those three pieces of information out into 3 additional fields using a query before /------------------------------\ |ProductDescription ...

TSQL to view, add and delete permissions

I have created several new tables on a new database(Microsoft SQL server 2005). Now those tables are ready for client users to view through Access 2003 by using System DSN ODBC object (with SQL authentication). What I did was to create a Role and a schema in the database, and a login user for my SQL server default with my database and th...