I'm in need of some help with getting my duplicates from showing more than once in my output. SELECT accountNumber AS 'Member Number', OD.orderdetails AS 'iNum', FirstName AS 'First Name', LastName AS 'Last Name', HGP.email AS 'Email', points AS 'Points -->', '$' + CONVERT(varchar(50),(CONVERT(int,Points) * .1)) AS...
Question: I have a view which I want to derive from a recursive query. The query is of the same structure as this one here: http://forums.asp.net/t/1207101.aspx And represents a treeview as an ordered dataset. How can I create a view which does this: ;WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS ( SELECT ID, [NAME], PARENT_I...
Consider this query: WITH Scores (score) AS ( SELECT CAST(score AS INTEGER) FROM ( VALUES (0), (10), (10) ) AS Scores (score) ) SELECT AVG(CAST(score AS DECIMAL(19, 8))) AS precision_eight, AVG(CAST(score AS DECIMAL(19, 7))) AS pr...
I have latitude and longitude defined as decimal(9, 6) in my zip code table, per the zip code database company's instructions. I take a lat/lon feed it to a function in my c# program and it gives me lat/lon in boundaries for getting a list of lat/lon from the database (radius distance) The sql code does not work with the longitude. SE...
I'm guessing this is not possible since the engine doesn't like it, but is there a way (barring dynamic SQL) to pass in a DATEPART as a parameter to a procedure? ...
I'm performing some large date-based delete queries on tables with indexes on fields other than date. I've been told that dropping those indexes, performing the delete, and then re-building the index could be faster than just running the delete against the indexed table. Is this the case? I can see why the actual removing of records wou...
I'm trying to create a simple stored procedure which queries a sys.tables table. CREATE PROCEDURE dbo.test @dbname NVARCHAR(255), @col NVARCHAR(255) AS SET NOCOUNT ON SET XACT_ABORT ON USE @dbname SELECT TOP 100 * FROM sys.tables WHERE name = @col GO This does not seem to work cause I should put GO ...
To make a long story short... I'm building a web app in which the user can select any combination of about 40 parameters. However, for one of the results they want(investment experience), I have to extract information from a different table and compare the values in six different columns(stock exp, mutual funds exp, etc) and return only...
Hi, we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it. Observations: Copying the code into a query window yields the query result in 1 second SP takes > 2.5 minutes until I cancel it Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT. Running sp_rec...
Hey, so a bit of a design question here: I am working on a project that requires me to keep track of when a user performs an Insert, Update, or Delete. The thing I am trying to decide on is whether to have the Logs table reference the Primary Key of the affected row or some other identifier. In order to log a deletion when using Forei...
I hava a stored procedure named PROC_fetchTableInfo (simple select from sys.tables) in a database named Cust I would like use this procedure in a another database named Dept I tried to execute this sp in this database using command EXECUTE Cust.dbo.PROC_fetchTableInfo but as a result I get tables from the database Cust. How to g...
What are the differences between SET vs. SELECT statement when assigning variables in T-SQL? ...
I'm a junior developer in our team. In one project we have about twenty databases in one SQL Server instance. We have db_owner rights to these databases. My intention is to monitor certain things from these databases (e.g. file size). But because we don't have sysadmin rights, we don't have all those management tools for these database...
Hi, I have to delete some amnt of data and insert some into same table. Will there be any performance improvement if we go for Upsert in a loop ? Thanks in advance! ...
UPDATE a SET CountOfAA=dt.CountOf FROM @MediaResurce a INNER JOIN (SELECT aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku ...
Hi All, I have a scenario where I want to get the distinct nodes from XML. So if I have this XML: <person> <age> <year value="2010"/> <month value="10"/> <day value="21"/> </age> <age> <year value="2011"/> <month value="11"/> <day value="4"/> </age> </person> How could I retrieve in the results: person age y...
UPDATE a SET CountOfAA=dt.CountOf, CountOfBB=dt.CountOf FROM @MediaResurce a INNER JOIN (SELECT aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf FROM @MediaResurce aa LEFT OUTER JOIN @MediaResurce_Pics bb ON aa.sku=bb.sku WHERE somec...
When creating a user defined function is it "bad" to just automatically use the largest string possible? For example, given the following UDF, I've used nvarchar(max) for my input string, where I know perfectly well that the function currently isn't going to need to accept nvarchar(max) and maybe I'm just forward thinking too much, but ...
I am trying to use BULK INSERT in SQL Server 2008 to import a TSV (Tab Separated Value) file. Here is my script: USE ABC GO CREATE TABLE CSVTest (ID INT, FirstName VARCHAR(40), LastName VARCHAR(40), TodaysDate DATETIME) GO BULK INSERT CSVTest FROM 'd:\csvtest.txt' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ) GO --Check the ...
Greetings.... lets say I have a table Employee like this EmpID, EmpName 1 , hatem and I write a query that select * from Employee for xml auto so the output will be in xml format I want some help to know how can I export the result to a xml file to be saved on my computer (HD) as I need to read the .xml files from this folder a...