tsql

T-SQL Pattern matching

I am trying to find a way to query rows of data by using a "multivalue" pipe delimited column in another table as a WHERE clause. SQL SERVER 2005 This is my best description of the problem: Imagine a pipe delimited column set to a variable like @LIST = 'Bob|Mary|Joe' then I am trying to find a match like this Select * from Users...

TSQL command to connect to another server (SQL Server 2005)

Is ther a TSQL command to connect to another server? Or when you are in a Query Window, what are the Keyboard shortcuts to connect to another server and have a Query Window show up? I have seen Ctrl+N pop up the Connect to Server dialog in some screens but when I am in a Query Window already and hit Ctrl+N it just opens up another Quer...

How can I convert text with a localized number format into a number in MS SQL Server?

Let's say I have a number in the german number format "1,00" which is equal to 1.0 in the en-US locale. Is there a built-in way to convert this text in T-SQL to the corresponding number? It seems like CONVERT and CAST accept only numbers with '.' as the decimal separator and I see no way to tell them otherwise. I was thinking about sim...

Web users searching for too much data

We currently have a search on our website that allows users to enter a date range. The page calls a stored procedure that queries for the date range and returns the appropriate data. However, a lot of our tables contain 30m to 60m rows. If a user entered a date range of a year (or some large range), the database would grind to a halt....

Check Contraint Bypassing CATCH block in Distributed Transaction

Hello, I have a MSSSQL stored procedure performing a distributed transaction that looks like this: SET XACT_ABORT ON; SET NOCOUNT ON; BEGIN TRY BEGIN DISTRIBUTED TRANSACTION insert into LNKSRV.INST.dbo.zz (id, val) values (1, 'a'); insert into LNKSRV.INST.dbo.zz (id, val) values (2, 'b'); COMMIT TRANSACTION END TRY BEGIN C...

How to use the SQL replace function effectively?

I am trying to replace multiple rows in an Access database to follow a new set of data rules. For instance, the word Fort in Fort Myers is listed as Ft., Ft and Fort. I would like to make a global change to the group. I am familiar with the SQL replace command, but wondering if anyone has done something similar with a stored procedure...

Need some help with a simple SQL query.

Hi folks, I've got a game which dumps state information to a database. Eg. Connected, Lost Connection, Grabbed Screen Dump, etc. Each state is defined by a field called StateTypeId, which is a foreign key to a simple lookup table 'StateTypes'. Now, i'm trying to find all the people who are currently 'Connected' (eg. StateTypeId == 1),...

Can I insert into a temp table the result of a stored procedure plus another value?

I have a temp table that needs the values of a Stored procedure. So the SP inserts 3 columns into the temp table, then I want to add a datetime to every row without modifying the SP. Since I call 3 times the SP each with a different datetime, I can't just update the whole temp table. Any Suggestions? DECLARE @temp TABLE ( ...

TSQL to LINQ conversion reference?

I'm trying to find a good reference to help me convert this TSQL statement to Linq: EXAMPLE: SELECT * FROM Categories WHERE ProductID IN (SELECT ProductID FROM ProductCategories WHERE CatID = 23) I can't find anywhere that references how to do the WHERE "IN" part. ...

Performance hit using ' where id IN [table] ' with only 1 row in [table]?

I have a stored procedure that takes a comma-delimited string of IDs. I split them and put them into a temporary table and pull out records from another table using where id IN [table] Is it ok to use this same procedure when only one id is passed in for the param? I could write a second stored procedure that would do exactly the sameth...

Table Group By - Tsql -

The first picture is my query. I need to obtain results in second picture. What to do? Which query do have to use? Select OrarioA, OrarioB, Max(IDOrario), (????)IDDettaglioOrarioA, (???)IDDettaglioOrarioB FROM TABLE GROUP BY OrarioA, OrarioB, ?????????????? I need respective IDDettaglioOrarioA, IDDettaglioOrarioB. How to complet...

How to write "Tagging" query in SQL?

Hi, I am adding "Tagging" functionality in my web app. My applications table structures are as following; Tag: (TagId INT IDENTITY, TagName VARCHAR(60)) TaggedRecords: (TaggedId INT IDENTITY, TagId, TaggedRecordId) Now, I want when anyone adds a tag to any record then following action should be performed using a single sql query...

How to create trigger

i'm using a sql server 2000. My Tree structured table is here tItem ItemID int keyfield, ParentItemID int, Title nvarchar 200, ChildCount int, Status int I need to calculate ChildCount and Status trigger. When i insert one item, parent item must calculate child count and status. 0 < status < 100 Calculate parent statu...

Levenshtein distance in T-SQL

I am interested in algorithm in T-SQL calculating Levenshtein distance. ...

Query Performance - Inner Join

I have this query and I want to improve performance: SELECT OrarioA, OrarioB, IDOrario, IDDettaglioOrarioA, IDDettaglioOrarioB FROM ( SELECT Tb_01.Orario AS OrarioA, Tb_02.Orario AS OrarioB, Tb_01.IDDettaglioOrariLinee AS IDDettaglioOrarioA, Tb_02.IDDettaglioOrariLinee AS IDDet...

SQL 2005 Select data where conversion fails

Is there any way for me to test a data conversion in a select statement and only return rows where the conversion fails? IE: SELECT * FROM my_table WHERE CONVERT(datetime, [colA]) = NULL I'm open to any SQL hacks/trickery. ...

How Do You Tell What Next Identity Column Will Be?

Is there a tsql query to tell what SQL server identity column value it expects to use for the next row insert? Edited to add: I deleted and recreated a table with [personID] [int] IDENTITY(1,1) NOT NULL as part of my CREATE TABLE command. I've also attempted to reseed identity columns while removing all information in that table a...

Inserting a dynamic number of rows into SQL Server

Is there a way to insert a dynamic number of rows from within sql server (.sql script) given the value of a look up, and setting one column for each insert? I want to attach a row with the foreign key of every row in a different table. For instance: table 1: 1 j k l m n 2-(fk) 2 j k l m n 3-(fk) 3 k u y k l 2-(fk) table 2: 2 hi you ...

Access denied when trying to move files with xp_cmdshell

Hi Im trying to use some T-SQL to move some files from one directory to another. Im using xp_cmdshell to call the move command Just like this: create table #output(line varchar(2000) null) insert into #output exec master..xp_cmdshell 'move /y "D:\files\*.txt" "D:\oldfiles"' But the files inst move and the #output table contains this o...

sql query to find customers who order too frequently?

My database isn't actually customers and orders, it's customers and prescriptions for their eye tests (just in case anyone was wondering why I'd want my customers to make orders less frequently!) I have a database for a chain of opticians, the prescriptions table has the branch ID number, the patient ID number, and the date they had the...