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...
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...
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...
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....
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...
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...
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),...
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
(
...
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.
...
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...
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...
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...
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...
I am interested in algorithm in T-SQL calculating Levenshtein distance.
...
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...
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.
...
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...
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
...
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...
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...