I have a list of addresses in two separate tables that are slightly off that I need to be able to match. For example, the same address can be entered in multiple ways:
110 Test St
110 Test St.
110 Test Street
Although simple, you can imagine the situation in more complex scenerios. I am trying to develop a simple algorithm that will ...
I am running a cross server insert
INSERT INTO server.database.dbo.table (Field) VALUES('test')
Afterward, I need to get the id of the last insert. However, when I run the the scope_identity function, I don't get the latest id from the foreign server.
SELECT @ID=SCOPE_IDENTITY()
How would I retrieve the last id from a cross server ...
Right now I have this SQL query which is valid but always times out:
SELECT
(
SELECT (MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME))
FROM WAYPOINTS
WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
AND WP_VTDID = 'L088'
)
AS MAXD,
(
SELECT MAX(WP_ODOMETER)
FROM WAYPOINTS
WHER...
I've just gone blank. I have a many to many relationship with three tables. Lets call them A, B and C. C stores the primary keys of the other two tables.
Now, I would like to insert all primary keys from table A, and lets say primary key 1 from B into table C. I just don't get it. All I came up with was some stored procedure that runs ...
I recently answered this question how-to-call-user-defined-function-in-order-to-use-with-select-group-by-order-by
My answer was to use an inline view to perform the function and then group on that.
In comments the asker has not understood my response and has asked for some sites / references to help explain it.
I've done a quick googl...
In a similar vein to my previous question I again ask the SO guys for your collective wisdom and help.
In a stored procedure and after passing some checks I need to insert a new row and return the newly created id for it. The check if a row exists works so it is the bit after that which I am undecided upon.
The table has two important ...
I need to write a stored procedure to update one of a set of similar columns. The columns are named 'UserField1', 'UserField2' etc. I was hoping to pass a parameter to the SPROC which would set the column to be updated. However, I can't seem to get the code correct. Here's a simplified example of what I tried (which gets me an 'Incor...
From an efficiency and best practices point of view, I appreciate everyones thoughts.
I have a stored procedure that makes (n) copies of a row and inserts the row into the necessary table.
For example. Make a copy of an inventory item and add the copies to inventory.
I see two options for this type of stored procedure.
Option 1:
CR...
In SQL Server, what are Instead Of triggers?
...
I'd need to know how to parse a query to know if it is well build before executing it. If the parsing is correct then execute it, if not discard it.
I haven't been able to accomplish this using SET NOEXEC, SET PARSEONLY, TRY/CATCH.
I m using dynamic sql inside a loop. Sometimes the dynamic sql is incorrect not for my fault but for the ...
I have a T-SQL stored procedure with the signature
CREATE PROCEDURE MyProc
@recordCount INT OUTPUT
@param1 INT
...
When executed directly in Sql Server the procedure runs in under 5 seconds, returning a few result sets amounting to about 100 rows in total.
Calling this procedure using the ADO.NET SqlDataAdapter.Fill method to populat...
I have a SQL statement from my application. I'd like to know which locks that statement acquires; how can I do that with SQL server?
The statement has been involved in a deadlock, which I'm trying to analyze; I cannot reproduce the deadlock.
I'm running on MS SQL Server 2005.
...
I have two tables: Holdings and Transactions
Holdings data looks like:
06/30/2009, A, 100
06/30/2009, B, 1200
06/30/2009, C, 100
06/30/2009, D, 100
Transactions data looks like:
A, 06/05/2009, 100
B, 06/02/2009, 400
B, 06/13/2009, 400
B, 06/28/2009, 400
C, 06/17/2009, 100
D, 06/30/2009, 100
Ok, so what I need to accomplish is t...
I have a table. The table needs to store a number values about a location, so initially I had just the two columns without the incrementing column, giving the following:
RefID | TypeID
1 | 1
1 | 3
1 | 6
2 | 3
3 | 5
3 | 6
Where the first column is the reference for a location and the second is the actual values....
Is there a way to pass the DatePart parameter of DateDiff as a variable?
So that I can write code that is similar to this?
DECLARE @datePart VARCHAR(2)
DECLARE @dateParameter INT
SELECT @datePart = 'dd'
SELECT @dateParameter = 28
SELECT
*
FROM
MyTable
WHERE
DATEDIFF(@datePart, MyTable.MyDate, GETDATE()) < @dateParameter
The only ...
I am working on a pomotion database and below is what my CREATE TABLE steatment looks like:
CREATE TABLE [dbo].[sponsors]
(
[InstId] [bigint] NOT NULL,
[EncryptedData] [varbinary](44) NOT NULL,
[HashedData] [varbinary](22) NOT NULL,
[JobId] [bigint] NOT NULL,
CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED
(
...
Let's say I have a SQL Server 2000 table, any name will do, it's irrelevant for this topic. On this table I have a trigger that runs after update or insert.
The user is able to insert and update the table on which the trigger is attached, but not on other tables that the trigger targets.
If the user modifies data in the original table...
I'll begin by admitting that my problem is most likely the result of bad design since I can't find anything about this elsewhere. That said, let's get dirty.
I have an Activities table and an ActivitySegments table. The activities table looks something like:
activityid (ident) | actdate (datetime) | actduration (datetime) | ticket...
I have a table with a column whose values come from an Enumeration. I need to create a TSQL function to convert these values to "Friendly Names" upon retrieval.
Examples:
'DateOfBirth' --> 'Date Of Birth'
'PrincipalStreetAddress' --> 'Principal Street Address'
I need a straight TSQL UDF solution. I don't have the option of install...
Background
I have a dimension table that has a single record for each day. Each record has a primary key so example data would be:
Dimension Table
---------------
---------------------------------
| ID | DateTime |
---------------------------------
| 1083 | 04/10/2008 10:02:00 PM |
---------------------------------
...