tsql

Sql Server Development Server and Live

I have a database project that goes through iterations (only one so far) and I need to deploy a testing version to a live server. I'm not sure how to go about this. I can make all the changes in a copy and then remake those changes in the live version. That doesn't make sense. Is there a way to change a server name to an existing ser...

Treating empty elements as nulls in SQL Server's OPENXML function

I have the following (highly simplified) XML document that I am reading into my database using the OPENXML function: <root> <row> <bar>123</bar> </row> <row> <bar>0</bar> </row> <row> <bar></bar> </row> </root> I am importing in into the database like so: insert into [Foo] ([bar]) s...

T SQL Rotate row into columns

SQL 2005 using T-SQL, I want to rotate rows into columns. Sample script: Use TempDB Go CREATE TABLE [dbo].[CPPrinter_InkLevels]( [CPPrinter_InkLevels_ID] [int] IDENTITY(1,1) NOT NULL, [CPMeasurementGUID] [uniqueidentifier] NOT NULL, [InkName] [varchar](30) NOT NULL, [InkLevel] [decimal](6, 2) NOT NULL, CONSTRAINT [PK_C...

T-SQL get SELECTed value of stored procedure

In T-SQL, this is allowed: DECLARE @SelectedValue int SELECT @SelectedValue = MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1 So, it's possible to get the value of a SELECT and stuff it in a variable (provided it's scalar, obviously). If I put the same select logic in a stored procedure: CREATE PROCEDURE GetMyInt AS SELECT MyInt...

Update Multiple Tables at a Time - SQL Server/T-SQL

I have two tables, Customer and CustomerPhone. Single record in Customer can have multiple CustomerPhone records. As you see in the image below, Phone and Fax resides in CustomerPhone table whereas the rest of the fields resides in Customer table. If user want to edits a customer record, obviously i will have to update the record in Cus...

Regression tests for T-SQL stored procedures

Hi, I would like to regression test t-sql stored procedures. My idea is to specify for each SP multiple input parameter sets. The SP should be executed with these parameters, results should be written to disc. Next time the new results should be compared with results stored before. Does anybody know a good tool for something like that?...

Use a specific database depending on condition

Can I somehow use a specific database given a specific condition? To clarify I will give a naive example: CASE WHEN @dbnum = 1 THEN USE Db1 ELSE USE DefaultDb END ...

Help with constructing a conditional SQL statement

Hello, I want to construct a SELECT statement with a conditional IF. Like, IF there is no records with the language code 'Swedish': SELECT * FROM Entries WHERE Language = 'Swedish' THEN use 'English' SELECT * FROM Entries WHERE Language = 'English' How would I construct this statement using MSSQL? Thanks, Stefan ...

How do I add totals/subtotals to a set of results without grouping the row data?

I'm constructing a SQL query for a business report. I need to have both subtotals (grouped by file number) and grand totals on the report. I'm entering unknown SQL territory, so this is a bit of a first attempt. The query I made is almost working. The only problem is that the entries are being grouped -- I need them separated in the rep...

T-SQL Question - Counting and Average

I have a set of data that consists of a filenbr, open date and close date. I need to produce a summary table similar to the below, i need to count how many files belong to each day period, but i need those greater than 20 grouped together. I know how to get the datediff, what i'm stumbling on is how to get the 20+ and the % column 1 d...

How do you extract just date from datetime in T-Sql?

I am running a select against a datetime column in SQL Server 2005. I can select only the date from this datetime column? ...

How do I prevent a font from extending off the top of a textbox?

I'm using a barcode font (IDAutomationMC39H) in an SSRS 2005 report I'm developing, and while it renders fine in the preview in BIDS, if I export the results to PDF, print the results, or do any of the above (including preview) in Report Manager, the barcode font is extending off the top of the textbox. The barcode itself is nearly 20px ...

Assign the results of a stored procedure into a variable in another stored procedure

The title of this question is a bit misleading, but I couldn't summarize this very well. I have two stored procedures. The first stored procedure (s_proc1) calls a second stored procedure (s_proc2). I want to assign the value returned from s_proc2 to a variable in s_proc1. Currently, I'm calling s_proc2 (inside s_proc1) in this manner:...

Table/column names enclosed in square brackets, even though their names consist of legal characters

Square brackets allow you to use names for columns or aliases that contain characters not permitted for column names or aliases. a) I’ve noticed that lots of times table and column names are enclosed inside square brackets, even though their names consist of perfectly legal characters. Why is that? b) As far as I know, square brackets...

FULL TEXT Search – usefulness of Ranking

RANK column indicates how well a row matched the selection criteria. SELECT * FROM CONTAINSTABLE ( someTable, *, 'surf OR life' ) AS ct INNER JOIN someTable ON ct.[KEY] = someTable.i Assuming we run the above query, I would expect that the two rows returned by a query ( one containing string life and other containing stri...

How to Select Multiple Records from Multiple Tables at Once

I have two tables Customer and CustomerPhone. Customer usually has multiple phone numbers, so when i run select statement on customer 101, i will get multiple records due to the multiple phone numbers. As you see below all the "Phone" and "Fax" field belongs to CustomerPhone table. These are considered as two records in the CustomerPh...

SQL Server stored procedures - update column based on variable name..?

Hi, I have a data driven site with many stored procedures. What I want to eventually be able to do is to say something like: For Each @variable in sproc inputs UPDATE @TableName SET @variable.toString = @variable Next I would like it to be able to accept any number of arguments. It will basically loop through all of the inputs a...

T-SQL: How to know how much storage space is left in sql server express?

How is it possible to know how much of the "DB quota" imposed by SQL Server express edition (4GB in old versions, 10GB in 2008R2) is in use? Let's say I have a DB that is using 1GB, I want to know "9GB of free space". Is it there a T-SQL command for this? ...

Solving Slow Query

We are installing a new forum (yaf) for our site. One of the stored procedures is extremely slow - in fact it always times out in the browser. If I run it in MSSMS it takes nearly 10 minutes to complete. Is there a way to find out what part of this query if taking so long? The Query: DECLARE @BoardID int DECLARE @UserID int DECLARE @C...

T-SQL Picking up active IDs from a comma seperated IDs list

I have two tables "Product" having following structure: ProductID,ProductName, IsSaleTypeA, IsSaleTypeB, IsSaleTypeC 1, AAA, N, N, N 2, BBB, N, Y, N -- active 3, CCC, N, N, N 4, DDD, Y, N, N -- active 5, EEE, N, N, N 6, FFF, N, N, N 7, FFE, N, N, N 8, GGG, N, N, N 9, HHH, Y, N, N -- active The second table "ProductAllowed" having ...