tsql

Create a global static variable in SQL Server?

Is there a way to create a global variable in SQL Server so it's saved even the server is shut down, so I can use it in functions? Example from what I need: DECLARE @@DefaultValue bit This variable should never be deleted unless I explicityl do so. ...

How do I fix this SQL GROUP BY query?

I have the following query: SELECT dev.DeviceName, Count(dom.DomainID) AS CountOfDomains FROM tblDevices dev JOIN tblIPNumbers ip ON dev.DeviceName = ip.ServerName JOIN tblDomains dom ON dom.IPNumberID = ip.IPNumberID WHERE dom.PointerTo=0 AND dev.DeviceType='3' AND (dev.[System]='32' OR dev.[Syst...

SQL - Output a substring based on a search pattern ensuring first/last strings are full words

I have the following query in which I am searching within the XMLData type column. I want to return a substring of a node that either starts with the search criteria and X number of characters afterward ending on a full word or a substring which places the search criteria in the middle of the result with X number of characters before an...

Portable SQL that works in SQL Server, mySQL and postgreSQL

Hello, Am wondering if there are guidelines somewhere to write SQL code which is compatible in SQL Server, mySQL as well as postgreSQL. I am writing a program and the user has a choice of backend database. It will be really difficult for me to create separate queries for each and every db. I do not want to use an ORM or anything like th...

Precedence/weight to a column using FREETEXTTABLE in dymnamic TSQL

I have dynamic sql that perform paging and a full text search using CONTAINSTABLE which works fine. Problem is I would like to use FREETEXTTABLE but weight the rank of some colums over others Here is my orginal sql and the ranking weight I would like to integrate (I have changed names for privacy reasons) SELECT * FROM (SELECT TOP 10 T...

LINQ2SQL Doesn’t return the same result as T-SQL…

I’ve the following tables: Paciente -> PacienteTag -> Tag The typical N:M between Paciente and Tag with an intermediary table. I want to obtain how many patients have each tag. Simple: SELECT Tag.TagName, COUNT(PacienteTag.TagId) AS Totals FROM Tag LEFT JOIN PacienteTag ON Tag.TagId = PacienteTag.TagId GROUP BY Tag.TagName ORDER BY ...

updating multiple tables with triggers - SQL Server

I need to update my tableA based on the insertions in tableB. i have created a trigger which fires when a row is inserted into tableB and simply copies it to tableA (which is exactly what i want). But the problem comes when i update my existing records in tableB, the rows in tableA already exist with the same primary key. to deal with i...

SQL Server Month Totals

SQL Server newbie The following query returns SRA by Student and month only if there is a record for a student in Discipline table. I need a query to return all students and month totals even if there is no record for student in Discipline table. Any direction appreciated SELECT TOP 100 PERCENT MONTH(dbo.Discipline.DisciplineDate) AS ...

Compare two DATETIME only by date not time in SQL Server 2008

I have two tables (bank statement and client requests) where column [date] is type of DATETIME2(0). I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds). How can I dot that? I tried CAST and CONVERT but it didn't helped. Update: Marc_s's answer works DATEADD(dd, 0, D...

SQL OpenXML returns nothing when the XML doesn't fill every level

I'm having an issue with OPENXML in SQL Server 2005 where I'll get no results back if the XML doesn't have every tier available. An example would clear up my question: So we have XML like this: <Connection Name="DEFAULT" />' <Branch Name="A_Branch"> <Leaf Name="A_Leaf.OP" > <Property Name="A_Property" /> </Leaf> ...

SQL Server 2000 "Incorrect syntax near the keyword 'CASE'"

The error is return when the following stored proc is attempted to be executed: EXECUTE p_someProc --list of vars CASE WHEN @var1=1 AND @var2=1 THEN 3 WHEN @var2=1 THEN 2 WHEN @var1=1 THEN 1 END, --more vars There are other CASE functions included, though only a single error is spewed up which points to the first CASE. Wh...

SQLite equivalent of SQL Server DateAdd function

I need help reproducing the following SQL statement to a statement that SQLite understands. SELECT * FROM SomeTable WHERE [Date] >= DATEADD(day, -14, GETDATE()) Any help is much appreciated! ...

SQL Syntax: Casting a datatype that is stored as data

Hi, I have a table that looks like this: id datatype name value 0 nvarchar(255) myName 'Paul' 1 int age '30' 2 float(53) Custom1 0.5 3 float(53) Custom2 1.3 4 float(53) Custom3 2.7 I am wondering if it is possible to do something like the following where I cast ...

Simulation of Generate Scripts in sql server 2005+(without using Management Studio)

Is there any inbuilt store proc / any database program by which I can generate all the database object script(like stored procs, triggers, functions, tables etc.) from a particular database. I am using SQL SERVER 2005 + 2008 Thanks in advance ...

Problem with indexed view msql

hi I have problem with a counting column in my view. SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ColumnC, ModuloColAColB Query is similar to this MSDN example: http://msdn.microsoft.com/en-us/library/ms191432.aspx When I try to compile view I received error message like: "invalid ...

Does GetDate() get re-evaluated for each iteration within loop within a transaction?

I have a scenario where I am looping through a resultset within a transaction and I need to INSERT a unique datetime value within a table for each iteration through the resultset - will GetDate() be recalculated each time or will it only be calculated the first time and then be the same for each iteration through the loop? My pseudo-c...

How do you select a stored procedure's return value?

It seems like this would would be trivial, but my SQL is not up to par and I can't figure it out. I just want to select a returned scalar from a stored procedure. Basically like this: select dbo.sProcedure @param But that throws an error. It's not really an option to return a result set from within the procedure. Is it possible to do ...

Are there any built in SQL Server 2005 functions to serialize / deserialize string parameters to a table?

The big question is are there any "built in" SQL Server 2005 functions to serialize / deserialize string parameters to a table variables? The rest explains my need further, but may not be necessary. I have a SQL SP I would like to refactor. It currently accepts five customers ids and five sets of order ids relevant to those customers....

TSQL Query for analyzing Text.

I have a table that has ordernumber, cancelled date and reason. Reason field is varchar(255) field and it was written by many different sales rep and really hard to group by the reason category I need to generate a report to categorize cancelation reasons. What is the best way to analyse the reasons with TSQL? Sample of reasons entered ...

T-SQL's equivalent of Oracle's %TYPE operator?

When writing a procedure in PL/SQL, I can declare a parameter's type as myTable.myColumn%TYPE so that when I alter myColumn's type from say varchar2(20) to varchar2(50) I don't have to change the procedure's parameter type. Is there something similar in T-SQL? ...