I am using tSQLt (through Red Gate’s SQL Test version 1.0.0.455). tSQLt is installed on database A. I am trying to do a tSQLt.FakeTable on a table in database B on the same SQL server instance through a synonym on database A. Code: ALTER PROCEDURE [ErrorType109NonTankHasSizeOrVolume].[test AliasTest] AS…
Tag: tsql
script issue Transact-SQL
I Want to return All table names from a use data base but this just return a char the output is s s s s s s Answer You need to define the length of @tableName, by default it is set to 1 character.
How to sum Accounts
I have 3 tables: DimAccounts, DimTime and FactBudget. DimAccounts example: DimTime example: FactBudget example: In FactBudget are many Accounts just with type B. I need to calculate Debit and Credit Sums where Account type is S (Sum). Columns AccountFrom and AccountTo shows B Type Accounts from where to begin…
How to test linkedserver’s connectivity in TSQL
I need to write a procedure to collect data from several remote servers, I use linkedservers and OPENQUERY to gather data from servers, but sometimes I lose connection to some servers or I simply can’t connect them (e.g. remote server is offline)- and OPENQUERY results in time-outs in these cases. So I …
Altering column size in SQL Server
How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5) Answer
SQL datetime needs to read 00:00:00.000
I have the following piece of SQL: select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) which comes through as this format: 2012-02-29 23:59:59.000 I need the exact piece of code with the …
Select a column if other column is null
I need to select a field called ProgramID from a table and if the ProgramID is NULL then I need to select the value in the InterimProgramID from the same table and alias it as ProgramID. How can I …
SQL: Join tables on substrings
I have a table A with the string-column a and a table B with the string-column b. a is a substring of b. Now I want to join the the two tables on a and b. Is this possible? I want something like this: Select * from A,B where A.a *”is substring of”* B.b How can I write this in
How to make a list of T-SQL results with comma’s between them?
Suppose we have a simple query like this: If we have one record in the result set, I want to set variable @v to that one value. If we have two or more records, I’d like the results to be separated by a comma and a space. What is the best way to write this T-SQL code? Example: result set
Remove duplicate data in ‘date-ranged’ rows
I have a table like the following. The date range is used to know for what time period the rest of the Attributes were valid, the problem i have is that there are several consecutive time ranges where the Attributes ramain the same, what I would like is to obtain the same data but without the duplicate rows. …