tsql

mysql_real_escape_string alternative for SQL Server

Am wondering what is the equivalent in PHP for SQL Server escaping of strings? ...

Syntax for INSERTing into a table with no values?

I have a table created with the following schema: CREATE TABLE [dbo].[Visualizations] ( VisualizationID int identity (1,1) NOT NULL ) Since the table has no settable fields, I'm not sure how to insert a record. I tried: INSERT INTO [Visualizations]; INSERT INTO [Visualizations] () VALUES (); Neither work. What is the p...

Extracting data from XML in Sql Server column

I've just discovered (by necessity) that TSQL has some functionality to extract data from columns that contain XML. I've got a Sql Server column that contains XML data (though the column type isn't xml...it's varchar). I'm supposed to extract a subset of the data from the column. A simplified entry would look something like this: dec...

How do I prevent SQL Server from concatenating XML results with the same name

I am trying to generate an XML document using the XML features in SQL 2008 but I'm running into an issue where the document requires two nodes to have the same name but SQL Server automatically concatenates any nodes with the same name. For example: <Location> <Address>Some Street</Address> <Address>Some other info</Address> </Loca...

TSQL to know database role members

Hi, I am using SQL 2000 and SQL 2005. I want to know which logins have db_owner or db_accessadmin rights to which databases. I can click on users or database roles in every database to see that. Could this be done in an easier way using TSQL? Thanks in advance ...

Is there any simple way to format decimals in T-SQL?

I know it could be done trivially in a non-SQL environment [post-data processing, frontend, what have you], but that's not possible at the moment. Is there a way to take a decimal(5,2) and convert it to a varchar without the trailing zeroes/decimal points? For example: declare @number decimal(5,2) set @number = 123.00 select cast(@numbe...

Update selected records - noob

Hi, Sorry this is a bit of a noob question. I have the following select statement. I would like to update the values of ContactAssociate from 'Bob' to 'Jane'. Is it possible to do this? SELECT TOP (1500) ContactID, ContactNotes, ContactAssociate, ContactAppointment FROM tb_Contact WHERE (ContactAssociate = 'Bob') AND (ContactAppointmen...

TSQL Pseudo Random text generator

I am doing some performance testing on a SQL sproc and just want to bang out a quick data generator for testing. I am after a simple way to generate a pseudo random (true random not needed in this case) varchar field. Ideas I have so far is having a character definition of valid characters that can be used and then build the string fro...

Is it possible to JOIN with a var from a LEFT JOIN without destrorying rows?

My code generates a large query. A simple version is SELECT * FROM main_table as mt JOIN user_data AS ud ON mt.user_id=ud.id LEFT JOIN ban_Status AS bs ON ud.status_id=bs.id JOIN AnotherTable ON bs.data=AnotherTable.id NOTE: This code is untested. When i remove the last join i get results. I can also change it to left join but that...

Get the id selected in an SP executed in an other SP

i have an SP that executes 1 SP at the moment EXEC mpSPAccess.PostIdSelect @PostDate = @TodaysDate The SP does something like this (very simplyfied :)) SELECT id FROM Post WHERE DateCreated = @PostDate After this SP is Executed i want to use the id i got from PostIdSelect as an parameter for more SPs Like this: EXEC mpSPAccess.Ge...

A simple but challenging SQL Question, at least I couldn't find a way out except doing it externally (c#).

Hello All, I have an SQL Table which consists of 1 column only Column Name A A A B B B B C D D E I need an SQL Code that returns the cut points. For the table above, it will return this: Column Name 3 7 8 10 11 3 is the end of A's and 7 is the end of B's and 8 is the end of C's and so on... Let's see what can...

LINQ to Entites: how can I implement this complex t-sql in LINQ (multiple joins, DISTINCT, NOT EXISTS) ?

I have a complex query that I'm trying to reproduce in LINQ to Entities, but I'm not there yet - is it possible? The t-sql query looks like: select distinct C.id, L.id from dp join L on L.fk = DP.id join M on ( M.l_code = L.l_code and M.dp_code = DP.dp_code ) join C on C.c_code = M.c_code where not exists ( select id from map whe...

using . in LIKE query

When i use . operator in like operator query is not selecting any of the records. here is my query. how to use " . " in LIKE . SELECT * FROM XSP_AssetList_V WHERE AccountID = '5d6b1eab-1697-de11-a2d1-00505617006d' AND PrinterSerialNumber LIKE '%13.12%' ...

SQL Server 2005 query multiple Access databases?

Is there a way to get SQL Server 2005 to query 3 or more separate Access databases (each returning 1 record) in one SQL statement? ...

Update table a from table b where (conditions)

Evening all, Actually, it's night. About 11pm. My brain is shutting down and I need a bit of help so I can finish and go home :) I have two tables - table a and table b. I need to update a field in table a with the value from a field in table b when two other fields match. The tables don't have a unique id for each record :( Basica...

SQL Server Recursion Question

I need to create links between pairs of items and rows of item pairs: ItemA ItemB ---------------- 1 2 1 3 4 5 4 6 6 2 7 8 9 2 9 10 11 12 11 13 14 15 Matching on either side of a pair constitutes a link: Link A B --------------- 1 1 2 1 1 3 1 4 5 ...

Optimize sql query

I have a sql that takes 7 min to execute. It processes a year of data but even so i feel it takes too long. Do you have any suggerences to optimize it? select count(s.numserviciomedico) AS total from Actos a, pacientes p, Historias h, serviciosmedicos s where p.codigo=h.codpaciente AND p.codigo=a.codPaciente AND ...

FORMSOF Thesaurus in SQL Server

Has anyone done any performance measures with this in terms of speed where there is a high number of substitutes for any given word. For instance, I want to use this to store common misspellings; expecting to have 4-10 variations of a word. <expansion> <sub>administration</sub> <sub>administraton</sub> <sub>aministraton</sub> <...

T-SQL copying a table variable

I'm trying to make a copy of a table variable: DECLARE @lt_Sections TABLE ( teamId SMALLINT NOT NULL ) DECLARE @lt_tempSections TABLE ( teamId SMALLINT NOT NULL ) -- populate some values in @lt_Sections -- take a copy of @lt_Sections SET @lt_tempSections = @lt_Sections This is giving me an error: Msg 137, Level 15, State 2, Li...

Optimize sql query

I have a sql that takes 1:20 min to execute. It processes a year of data but even so i feel it takes too long. I changed the IN uses with EXISTS as recommended for another query (in this case that optimization wasnt enought :S ) Do you have another suggerence to optimize it? select gd.descripcion,count(gd.descripcion) as total from dia...