I have a database that has a list of zip codes which has latitude, longitude, state, city, state fips code, time zone, etc. I'd like to extend this and write either a C# function or TSQL (SQL Server 2008) function that will take a street, city, state and zip and return the zip+4. I've searched high and low and there's many programs out t...
We had a Stored Procedure written and it had a GRANT written at the last line of the SP.. Our DBA recommended that there should be a GO before the GRANT statement, else it will be executed every time. What does this mean? I am not sure how GO will prevent GRANT executing "every time". ...
I have a complex view that I use to pull a list of primary keys that indicate rows in a table that have been modified between two temporal points. This view has to query 13 related tables and look at a changelog table to determine if a entity is "dirty" or not. Even with all of this going on, doing a simple query: select * from vwDirt...
I'm calling a webservice (url below) and am getting the XML results back within SQL Server as a varchar(8000) and then converting that to XML. This works perfectly. I want to parse this XML information out into it's individual values but continue to get null values. This is my first attempt in using XML on my SQL 2008 server so I know I'...
In (non-English) book on TSQL (MS SQL Server 2005) I read about: select * from ::fn_helpcollations() Though, execution of it without "::" select * from fn_helpcollations() in my MS SQL Server 2008 R2 gives exactly the same result. What does "::" mean in T-SQL? ...
I have this data: name qty date flag --------------------------------------------- abc 255 11/10/1986 12:00:00 AM IN abc 300 11/10/2010 12:00:00 AM IN abc 12 11/10/2012 12:00:00 AM OUT abc 13 11/9/2010 12:00:00 AM OUT NULL NULL NULL NULL I ...
I have below table structure in MS SQL AirQuoteID Name SalesValue 7 M 49.50 7 N 23.10 7 +45 233.20 7 +100 233.20 7 +250 2333.10 I want a query which can return AirQuoteID M N +45 ...
I'm using CONTAINSTABLE to search two table columns. Once the search contains small words like 'the' 'for' 'a' the search returns no results even when they are actually present in the column. Quick example. Column being searched contains the text. 'System needs to be upgraded' Following SQL returns 0 rows SELECT * FROM Incident WH...
Below mentioned stored procedure is giving error while creating Msg 156, Level 15, State 1, Procedure crosstab, Line 23 Incorrect syntax near the keyword 'pivot'. Can anyone please tell me the mistake? Below is the script: CREATE PROCEDURE crosstab @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(...
I want to use a table valued function and call it from within multiple stored procedures rather than repeat the same query across all the stored procedures, but for compatibility with a legacy VB6/UltraGrid app I need to preserve the foreign key references from the original tables. Within the table valued function I can specify a PRIMAR...
To fill one variable with a value from a query I can write following: SET @TargetID = (SELECT TOP 1 ID FROM @bigDataSet ORDER BY date DESC) To fill multiple variables from this query, eg. something like: SET (@TargetID, @TargetName) = ....(SELECT TOP 1 ID, [Name] FROM @bigDataSet ORDER BY date DESC) what can I write? ...
I have a problem to get the max and the min value, I want the result to be XXL and XXS SELECT MAX(tblSizeXL.SizeXLName) AS maxSize, MIN(tblSizeXL.SizeXLName) AS minSize FROM Product JOIN tblSizeXL ON Product.SizeXLID = tblSizeXL.SizeXLID WHERE (Product.GroupID = @GroupID) GROUP BY tblSizeXL.SizeXLID ORDER BY ...
I have a database containing addresses, one address per Row. Contains every address in the UK, so ~28 million rows. One of the columns is 'Street', which I am using to perform searches. I have a non-unique, non-clustered index on this column. I'm having inconsistencies with search speeds however. select * from Postcodes where Street = ...
I have this query: select * from table where column like '%firstword[something]secondword[something]thirdword%' What do I replace [something] with to match an unknown number of spaces? Edited to add: % will not work as it matches any character, not just spaces. ...
I have a procedure with a (slightly more complex) version of the below: CREATE PROC sp_Find_ID ( @Match1 varchar(10), @Match2 varchar(10) ) AS DECLARE @ID int SELECT @ID = ID FROM Table1 WHERE Match1 = @Match1 AND Coalesce(Match2,@Match2,'') = Coalesce(@Match2,Match2,'') SELECT @ID ID Essentially Match1 is a mandatory m...
My SQL Server view SELECT geo.HyperLinks.CatID, geo.Tags.Tag, geo.HyperLinks.HyperLinksID FROM geo.HyperLinks LEFT OUTER JOIN geo.Tags INNER JOIN geo.TagsList ON geo.Tags.TagID = geo.TagsList.TagID ON geo.HyperLinks.HyperLinksID = geo.TagsList.HyperLinksID WHERE HyperLinksID = 1 returns these... HyperLinksID...
I am trying to figure out how to compare the current day's data to the same data from a week ago, 2 weeks, etc. Let's say I have a table called "Order" with 2 columns: Order table ----------- OrderID int identity OrderDate datetime If today, is Monday, I would like to be able to compare the number of orders from today to the previous...
I assume that by intelligent design the source adapter is parameterized but just wanted confirmation from an SSIS veteran. As an example if I have a source query in the adapter like: select files ,transactions ,dateofsomething from reallygoodtablename where theparameter = ? Does this get prepared by sql and get a cached plan or do...
Before I get any militant cursor-bashing, let me say that I'm trying to use nested cursors to do something I only need to do once, but if I ran the operative stored procedure once for each user and agency I have to do it a few hundred times. I thought a nested cursor in this case would save me some work, however, when I run this script ...
Hello, I have the following output in a query. SKILL LEVEL SCORERANGE ----------------------------------------------------------------------------- Stunts LOW 0.0 - 4.0 Stunts MED ...