Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms (1)

10
votes
2answers
13k views

Transact SQL using WITH in CREATE VIEW

I want to create VIEW using WITH clauses, but really can't find any references on correct syntax. I want smth like this WITH TempTbl AS (SELECT ...) CREATE VIEW SomeView SELECT * FROM TempTbl And ...
0
votes
1answer
205 views

How to find “outer join in the where” syntax in SQL 2005 using cmptlevel 80?

Well last Christmas I was very happy, because we ceased support for SQL Server 2000. I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored ...
3
votes
2answers
1k views

AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2

Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2? If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date? ...
8
votes
2answers
6k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
2
votes
2answers
865 views

Why is query using Clustered Index when it shouldn't?

Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
1
vote
2answers
418 views

How do I record SQL transactions with SQL Server Express

With a test migration from 2000 to 2008R2 Express, one function from the client application fails silently. I'd like to find out what SQL is running and why it fails. I think I would have attacked ...
8
votes
3answers
5k views

Using Row_Number to find consecutive row count

I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row If my data looks like this 724 727 728 733 735 737 743 747 ...
0
votes
1answer
501 views

Small Squares or Blocks after string in table field

My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query ...
2
votes
1answer
83 views

How to get dependencies order

I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
0
votes
1answer
123 views

What would cause the speed difference between servers?

I have this query on my dev server: select snStudyNumber as ProjectNumber from CDB.cdb.Quotes q inner join CDB.cdb.LineItems l on q.PK_quID=l.FK_quID inner join ...
2
votes
2answers
241 views

What is an industry standard method for verifying openquery connection is open prior to running queries?

I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well. I'm seeking an ...
1
vote
1answer
715 views

What is SQL/PSM and how does it differ from other versions of SQL?

I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL ...
0
votes
1answer
457 views

How to start and then stop one cyclic process

I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
18
votes
4answers
53k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
2
votes
1answer
1k views

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
5
votes
1answer
747 views

T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem. Here's the scenario: Users can set up service instructions to be attached to a work order ...
4
votes
4answers
699 views

Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?" We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
5
votes
3answers
1k views

How do I row count into groups?

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
5
votes
1answer
839 views

SELECT INTO creates a new table?

I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
5
votes
1answer
863 views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
10
votes
1answer
1k views

Generating large strings for test data

I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
18
votes
3answers
7k views

Why is `SELECT @@IDENTITY` returning a decimal?

I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application. INSERT INTO Customers ( Type, Name, Address, ...
4
votes
1answer
666 views

Does T-SQL MOVE actually move the database, or just copy?

I'm a bit confused over the T-SQL command MOVE. I have a statement saying: MOVE N'Prod_Database_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf' ...
8
votes
3answers
447 views

When are procedural queries absolutely necessary?

I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
2
votes
2answers
4k views

Getting current date as part of an Stored Procedure

I have the following SP: SELECT moncallAdd.FirstListing, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart, ...
6
votes
1answer
6k views

Difference between EXEC() and sp_executesql with no parameters?

I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server. But, I'm wondering what the difference is ...
7
votes
1answer
5k views

Find all ancestor nodes of a HierarchyId using a single SQL statement?

I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
2
votes
2answers
248 views

Creating a new role

How do I create a new role in my database with my first name which is Denise, and add read only access on the HR user's EMPLOYEES table to this role.
1
vote
2answers
141 views

Determine free space on “nonfixed” drive

I am writing a stored procedure that needs to send an email when a dvd rw drive is almost full. I found master.sys.xp_fixeddrives but it only shows free disk space for fixed hard drives. I have not ...
2
votes
2answers
1k views

Filters Joins or Where clauses TSQL

Where should i put the filters on a tsql query SELECT a.colum FROM A JOIN B ON A.ID = B.ID AND B.STATUS = 1 OR SELECT a.colum FROM A JOIN B ON A.ID = B.ID WHERE B.STATUS = 1 Im ...
3
votes
5answers
205 views

How can I store function names to be executed on a subset of rows?

I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations ...
3
votes
2answers
374 views

Search on part of a column in a SQL Server cross-database view

I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
6
votes
2answers
1k views

How to change SQL sever configuration manager settings using TSQL?

SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc... Is it possible to make these changes that is done in SQL server ...
5
votes
1answer
759 views

Why can the target of the OUTPUT INTO clause not be a view?

Here is my statement: DELETE FROM table1 OUTPUT deleted.col1, deleted.col2 INTO view1; It gives me this error: The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
4
votes
2answers
3k views

How can I get a list of column names and types from a resultset?

Say I get a resultset back from the following query: select * from sys.database_files; For any given resultset, I want to be able to query the column names and types so I can then create tables to ...
3
votes
2answers
852 views

MS SQL : Use Computed value to Compute other values

I'm having problem with generating results which are computed from data that exists in database. This is the case I have Coupons that can be amount or percent, if percent is 0 then amount is used ...
26
votes
7answers
48k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
5
votes
3answers
3k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
11
votes
5answers
4k views

Does it make sense to use SQL Server's bracket notation in hand written code?

Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything. When I first saw it, I though wow a reincarnation of the somewhat ...