Search Results

Search found 1208 results on 49 pages for 'tsql'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 18/49 | < Previous Page | 14 15 16 17 18 19 20 21 22 23 24 25  | Next Page >

  • SQL Self Join Query Help

    - by hdoe123
    Hi All, I'm trying to work out a query that self join itself on a table using the eventnumber. I've never done a self join before. What i'm trying to query is when a client has started off in a city which is chester to see what city they moved to. But I dont want to be able to see if they started off in another city. I would also like be only see the move once (So i'd only like to see if they went from chester to london rather then chester to london to wales) The StartTimeDate is the same EndDateTime if they moved to another city. Data example as follows if they started off in the city chester :- clientid EventNumber City StartDateTime EndDateTime 1 1 Chester 10/03/2009 11/04/2010 13:00 1 1 Liverpool 11/04/2010 13:00 30/06/2010 16:00 1 1 Wales 30/07/2010 16:00 the result I would like to see is on the 2nd row - so it only shows me liverpool. Could anyone point in the right direcetion please?

    Read the article

  • How to modify the attributes by putting dynamic node paths

    - by sam
    I have a code that selects all elements and their child nodes DECLARE @x XML DECLARE @node_no int DECLARE @count int DECLARE @max INT, @i INT EXECUTE return_xml '1', NULL, @x output Declare @temp Table ( id int not null identity(1,1), ParentNodeName varchar(max), NodeName varchar(max), NodeText varchar(max) ) INSERT INTO @temp SELECT t.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName, t.c.value('local-name(.)', 'varchar(max)') AS NodeName, t.c.value('text()[1]', 'varchar(max)') AS NodeText FROM @x.nodes('/booking//*') AS t(c) select * from @temp Now I want to modify the attributs by putting dynamic node paths SET @x.modify (' insert attribute MyId {sql:variable("@i")} as first into (ParentNodeName/NodeName::*[position() = sql:variable("@i")])[1] ') where id = id of temp table any Idea how can I modify my whole xml this way as I am having a untyped xml and have to add an attribute in every node

    Read the article

  • interval overlapping in tsql

    - by Nico
    hi folks, i need to get splited intervals and the number of overlapping intervals, eg basedata: interval A: startTime 08:00, endTime 12:00 interval B: startTime 09:00, endTime 12:00 interval C: startTime 12:00, endTime 16:00 interval D: startTime 13:00, endTime 14:00 now i have a separate interval from 10:00 to 15:00 and have to determine what intervals are intersected at first. result should be something like: 1: 10:00 - 12:00 ( intersecting with interval A ) 2: 10:00 - 12:00 ( intersecting with interval B ) 3: 12:00 - 15:00 ( intersecting with interval C ) 4: 13:00 - 14:00 ( intersecting with interval D ) this part works fine, the following causes the trouble: i need some kind of weighting for parallel intervals. this also means, that it can occur that an interval-intersection must be splitted n times, if it's ( partly ) intersected by another one. in the upper example the expecting result would be: 1: 10:00 - 12:00 -> weightage: 50% 2: 10:00 - 12:00 -> weightage: 50% 3.1: 12:00 - 13:00 -> weightage: 1oo% 3.2: 13:00 - 14:00 -> weightage: 50% 3.3: 14:00 - 15:00 -> weightage: 50% 4: 13:00 - 14:00 -< weightage: 100% the splitting of interval 3 is caused by the intersecting with interval 4 between 13:00 and 14:00. sql-server is ms-sql 2008. thanks for help in advance!

    Read the article

  • Create a user-defined aggregate without SQL CLR

    - by David Pfeffer
    I am planning on deploying a database to SQL Azure, so I cannot use the SQL CLR. However, I have a need to create an aggregate function -- in my case, I need to STUnion a bunch of Geography objects together. (Azure is expected to support Spatial by June.) Is there another way to accomplish this, without making use of the CLR, in a query? Or do I have to create a UDF that will take a table as a parameter and return the aggregate?

    Read the article

  • Why is RAISERROR misspelled? Or is it not?

    - by Jason
    Why isn't RAISERROR spelled RAISEERROR? Where is the second E? I could understand if it were some ancient keyword length constraint, but I wouldn't expect it to be a nine-character limit. Is RAIS or RROR a technical word such that "raise-error" is just a mis-reading? Are its (immediate) origins in a different language? I've searched Google but not finding much on the subject.

    Read the article

  • SQL update query using joins

    - by Shyju
    I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number ,mm.ManufacturerID from item_master im, group_master gm, Manufacturer_Master mm where im.mf_item_number like 'STA%' and im.sku=gm.sku and gm.ManufacturerID = mm.ManufacturerID and gm.manufacturerID=34 I want to update the mf_item_number field values of table master with some other value which is joined in the above condition. How to frame the SQL ?

    Read the article

  • Data tweaking code runs fine when executed directly - but never stops when used in trigger

    - by MBaas
    I have written some code to ensure that items on an order are all numbered (the "position number" or "item number" has been introduced only recently and we did not want to go and change all related code - as it is "asthetics only" and has no functional impact.) So, the idea is to go and check for an records that jave an itemno of NULL or 0 - and then compute one and assign it. When executing this code in a query window, it works fine. When putting it into an AFTER INSERT-trigger, it loops forever. So what is wrong here? /****** Objekt: Trigger [SetzePosNr] Skriptdatum: 02/28/2010 20:06:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [SetzePosNr] ON [dbo].[bestellpos] AFTER INSERT AS BEGIN DECLARE @idb int DECLARE @idp int DECLARE @pnr int SELECT @idp=id,@idb=id_bestellungen FROM bestellpos WHERE posnr IS NULL OR posnr=0 WHILE @idp IS NOT NULL BEGIN SELECT @pnr = 1+max(posnr) FROM bestellpos WHERE id_bestellungen = @idb print( 'idp=' + str(@idp) + ', idb=' + str(@idb) + ', posnr=' + str(@pnr)) UPDATE bestellpos SET posnr=@pnr WHERE id=@idp SELECT @idp=id,@idb=id_bestellungen FROM bestellpos WHERE posnr IS NULL OR posnr=0 END END

    Read the article

  • How do I prevent a font from extending off the top of a textbox?

    - by David Ellis
    I'm using a barcode font (IDAutomationMC39H) in an SSRS 2005 report I'm developing, and while it renders fine in the preview in BIDS, if I export the results to PDF, print the results, or do any of the above (including preview) in Report Manager, the barcode font is extending off the top of the textbox. The barcode itself is nearly 20px tall, even at only a 12pt size. I've tried changing the CanGrow value for the textbox, but it seems that even with that set off the barcode isn't pushing the bottom edge of the textbox downwards, it's simply extending over the top of the box. I've also tried resizing the textbox to allow it to contain the entire height of the font, but even with the VerticalAlign set to Bottom the barcode still sticks to the top of the textbox and overflows out the top. See screenshot here: http://bit.ly/9UfycP Does anyone know how to force the font to be bound by the top of the box, instead of overlapping it?

    Read the article

  • Retrieving a filtered list of stored procedures using t-sql

    - by DanDan
    I'm trying to get a list of stored procedures in t-sql. I am using the line: exec sys.sp_stored_procedures; I would like to filter the results back though, so I only get user created stored procedures. I would like to filter out sp_*, dt_*, fn_*, xp_* and everything else that is a system stored procedure and no interest to me. How can I manipulate the result set returned? Using Sql Server 2008 express. Solved! Here is what I used: SELECT name FROM sys.procedures WHERE [type] = 'P' AND name NOT LIKE 'sp_%' AND name NOT LIKE 'dt_%' ORDER BY name ASC;

    Read the article

  • How can I work around SQL Server - Inline Table Value Function execution plan variation based on par

    - by Ovidiu Pacurar
    Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in my case) the execution plan is altered an the query goes from 1 sec to 1 minute execution time. A proof of concept table - 1K products, 2M rows: CREATE TABLE [dbo].[POC]( [Date] [datetime] NOT NULL, [idProduct] [int] NOT NULL, [Quantity] [int] NOT NULL ) ON [PRIMARY] The inline table value function: CREATE FUNCTION tdf (@p_date datetime) RETURNS TABLE AS RETURN ( SELECT idProduct, SUM(Quantity) AS TotalQuantity, max(Date) as LastDate FROM POC WHERE (Date < @p_date) GROUP BY idProduct ) The scalar value function: CREATE FUNCTION [dbo].[EndOfDay] (@date datetime) RETURNS datetime AS BEGIN DECLARE @res datetime SET @res=dateadd(second, -1, dateadd(day, 1, dateadd(ms, -datepart(ms, @date), dateadd(ss, -datepart(ss, @date), dateadd(mi,- datepart(mi,@date), dateadd(hh, -datepart(hh, @date), @date)))))) RETURN @res END Query 1 - Working great SELECT * FROM [dbo].[tdf] (getdate()) The end of execution plan: Stream Aggregate Cost 13% <--- Clustered Index Scan Cost 86% Query 2 - Not so great SELECT * FROM [dbo].[tdf] (dbo.EndOfDay(getdate())) The end of execution plan: Stream Aggregate Cost 4% <--- Filter Cost 12% <--- Clustered Index Scan Cost 86%

    Read the article

  • T-SQL Table Joins - Unique Situation

    - by Dimitri
    Hello Everyone. This is my first time encountering the case like this and don't quite know how to handle. Situation: I have one table tblSettingsDefinition, with fields: ID, GroupID, Name, typeID, DefaultValue. Then I have tblSettingtypes with fields TypeID, Name. And I have final table, tblUserSettings with fields SettingID, SettingDefinitionID, UserID, Value. The whole point of this is to have customizable settings. Setting can be defined for a Group or as global setting (if GroupID is NULL). It will have a default value, but if user modifies the setting, an entry is added to tblUserSettings that stores new value. I want to have a query that grabs user settings by first looking at the tblUserSettings, and if it has records for the given user, grabs them, if not retrieves default settings. But the trick is that no matter if user has settings or not, I need to have fields from other two table retrieved to know the setting's Type, Name etc... (which are stored in those other tables). I'm writing query something like this: SELECT * FROM tblSettingDefinition SD LEFT JOIN tblUserSettings US ON SD.SettingID = US.SettingDefinitionID JOIN tblSettingTypes ST ON SD.TypeID=ST.ID WHERE US.UserID=@UserID OR ((SD.GroupID IS NULL) OR (SD.GroupID=(SELECT GroupID FROM tblUser WHERE ID=@UserID))) but it retrieves settings for all users from tblUserSettings instead of just ones that match current @UserID. And if @UserID has no records in tblUserSettings, still, all user settings are retrieved instead of the defaults from tblSettingDefinition. Hope I made myself clear. Any help would be highly appreciated. Thank you.

    Read the article

  • Dynamically call a stored procedure from another stored procedure

    - by Greg
    I want to be able to pass in the name of a stored procedure as a string into another stored procedure and have it called with dynamic parameters. I'm getting an error though. Specifically I've tried: create procedure test @var1 varchar(255), @var2 varchar(255) as select 1 create procedure call_it @proc_name varchar(255) as declare @sp_str varchar(255) set @sp_str = @proc_name + ' ''a'',''b''' print @sp_str exec @sp_str exec call_it 'test' So procedure call_it should call procedure test with arguments 'a', and 'b'. When I run the above code I get: Msg 2812, Level 16, State 62, Procedure call_it, Line 6 Could not find stored procedure 'test 'a','b''. However, running test 'a','b' works fine.

    Read the article

  • SQL Server: collect values in an aggregation temporarily and reuse in the same query

    - by Erwin Brandstetter
    How do I accumulate values in t-SQL? AFAIK there is no ARRAY type. I want to reuse the values like demonstrated in this PostgreSQL example using array_agg(). SELECT a[1] || a[i] AS foo ,a[2] || a[5] AS bar -- assuming we have >= 5 rows for simplicity FROM ( SELECT array_agg(text_col ORDER BY text_col) AS a ,count(*)::int4 AS i FROM tbl WHERE id between 10 AND 100 ) x How would I best solve this with t-SQL? Best I could come up with are two CTE and subselects: ;WITH x AS ( SELECT row_number() OVER (ORDER BY name) AS rn ,name AS a FROM #t WHERE id between 10 AND 100 ), i AS ( SELECT count(*) AS i FROM x ) SELECT (SELECT a FROM x WHERE rn = 1) + (SELECT a FROM x WHERE rn = i) AS foo ,(SELECT a FROM x WHERE rn = 2) + (SELECT a FROM x WHERE rn = 5) AS bar FROM i Test setup: CREATE TABLE #t( id INT PRIMARY KEY ,name NVARCHAR(100)) INSERT INTO #t VALUES (3 , 'John') ,(5 , 'Mary') ,(8 , 'Michael') ,(13, 'Steve') ,(21, 'Jack') ,(34, 'Pete') ,(57, 'Ami') ,(88, 'Bob') Is there a simpler way?

    Read the article

  • How to write a SQL query for numerous conditions with lots of fields in ASP.NET

    - by Yongwei Xing
    Hi all I have a ASP.NET site. There is a table in the SQL Server with more than 30 fields. I need make a page on which there are many filters to query data from database based on the filters you select or input. One filter for one fields in the database. The filter would be dropdown list, textbox,checkbox or listbox. If you do not choose one filter, it means select all for this field. So there are lots of combination for these fields. Is there any simple way to write such page and query for this requirement? Best Regards,

    Read the article

  • Dynamic openrowset in T-Sql Function or viable alternative?

    - by IronicMuffin
    I'm not quite sure how to phrase this. Here is the problem: I have 1-n items that I need to join to a different system (AS400) to get some data. The openrowset takes forever if I specify the where criteria outside of the openrowset, e.g.: select * from openrowset('my connection string', 'select code, myfield from myTable') where code = @code My idea was to create a function that takes in the item number and uses dynamic sql to inject it into the openrowset string, a la: declare @cmd varchar(1000) set @cmd = 'select * from openrowset('my connection string', ''select code, myfield from myTable where code = ' + @code + ''')' Apparently I can't use the insert.. exec.. strategy inside of a function. Is there any better way to achieve this? I was going to use this in joins where I needed the external data using cross apply. I'm not married to tvf and cross apply, but I do need a method of getting this data quickly. Thanks for any help.

    Read the article

  • Select Statement to show missing records (Easy Question)

    - by Gerhard Weiss
    I need some T-SQL that will show missing records. Here is some sample data: Emp 1 01/01/2010 02/01/2010 04/01/2010 06/01/2010 Emp 2 02/01/2010 04/01/2010 05/01/2010 etc... I need to know Emp 1 is missing 03/01/2010 05/01/2010 Emp 2 is missing 01/01/2010 03/01/2010 06/01/2010 The range to check will start with todays date and go back 6 months. In this example, lets say today's date is 06/12/2010 so the range is going to be 01/01/2010 thru 06/01/2010. The day is always going to be the 1st in the data. Thanks a bunch. :) Gerhard Weiss Secretary of Great Lakes Area .NET Users Group GANG Upcoming Meetings | GANG LinkedIn Group

    Read the article

  • How to migrate large amounts of data from old database to new

    - by adam0101
    I need to move a huge amount of data from a couple tables in an old database to a couple different tables in a new database. The databases are SQL Server 2005 and are on the same box and sql server instance. I was told that if I try to do it all in one shot that the transaction log would fill up. Is there a way to disable the transaction log per table? If not, what is a good method for doing this? Would a cursor do it? This is just a one-time conversion.

    Read the article

  • SQL 2008: Using separate tables for each datatype to return single row

    - by Thomas C
    Hi all I thought I'd be flexible this time around and let the users decide what contact information the wish to store in their database. In theory it would look as a single row containing, for instance; name, adress, zipcode, Category X, Listitems A. Example FieldType table defining the datatypes available to a user: FieldTypeID, FieldTypeName, TableName 1,"Integer","tblContactInt" 2,"String50","tblContactStr50" ... A user the define his fields in the FieldDefinition table: FieldDefinitionID, FieldTypeID, FieldDefinitionName 11,2,"Name" 12,2,"Adress" 13,1,"Age" Finally we store the actual contact data in separate tables depending on its datatype. Master table, only contains the ContactID tblContact: ContactID 21 22 tblContactStr50: ContactStr50ID,ContactID,FieldDefinitionID,ContactStr50Value 31,21,11,"Person A" 32,21,12,"Adress of person A" 33,22,11,"Person B" tblContactInt: ContactIntID,ContactID,FieldDefinitionID,ContactIntValue 41,22,13,27 Question: Is it possible to return the content of these tables in two rows like this: ContactID,Name,Adress,Age 21,"Person A","Adress of person A",NULL 22,"Person B",NULL,27 I have looked into using the COALESCE and Temp tables, wondering if this is at all possible. Even if it is: maybe I'm only adding complexity whilst sacrificing performance for benefit in datastorage and user definition option. What do you think? Best Regards /Thomas C

    Read the article

  • Removing a default value from a Sql Server 2005 table

    - by Nikola Stjelja
    Hi, I have a tabel in an Sql Server 2005 database. I have the following column IndPL INT DEFAULT 0 NULL I want to change the column to be of the type NVARCHAR but I receive a constraint violation due to the fact that the column has a default value constraint attached to it. I need to find out how to remove a default value constraint from a table column or how to change the column type without impeding the constraint.

    Read the article

  • Why might SQL execute more quickly on SQL Server 2000 when NOT using a stored procedure?

    - by Kofi Sarfo
    I could see nothing wrong with the execution plan. Besides, as I understand it, SQL Server 2000 extended many of the performance benefits of stored procedures to all SQL statements by recognising new T-SQL statements against T-SQL statements of existing execution plans (by retaining execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans) It's a fairly straight forward SELECT statement with sensible table joins, no transactions included or linked servers being referenced within the query and WITH (NOLOCK) table hints applied. The stored procedure was created by dbo and the user has all the necessary permissions. So my question is this: What are the likely reasons for a query to take only a few seconds to run but then take several minutes when identical T-SQL is run via a stored procedure?

    Read the article

  • Check if I can execute some sql-command

    - by Alexander Stalt
    I'm using ADO .NET and MS SQL Server 2008. I have a connection object to a server and a command: SqlConnection conn = /* my connection*/; string cmd = "some_sql_command"; I want to check if SQL Server can execute cmd. I don't want to execute cmd, but I want to know If SQL Server can execute it. cmd can be any single SQL statement, it's not a procedure, transaction or multiple commands etc..

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 14 15 16 17 18 19 20 21 22 23 24 25  | Next Page >