Search Results

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

Page 31/49 | < Previous Page | 27 28 29 30 31 32 33 34 35 36 37 38  | Next Page >

• Insert multiple rows into temp table with one command in SQL2005

  - by Adam Haile

  I've got some data in the following format: -1,-1,-1,-1,701,-1,-1,-1,-1,-1,304,390,403,435,438,439,442,455 I need to insert it into a temp table like this: CREATE TABLE #TEMP ( Node int ) So that I can use it in a comparison with data in another table. The data above represents separate rows of the "Node" column. Is there an easy way to insert this data, all in one command? Also, the data will actually being coming in as seen, as a string... so I need to be able to just concat it into the SQL query string. I can obviously modify it first if needed.

  Read the article

• How can I update many rows with SQL in a single table?

  - by tmarouda

  Hi folks. I have a table and one of the columns holds web addresses like: 'http://...' or 'https://...'. The problem is that there are some invalid entries, like 'shttp://...' or '#http//...' (the first character is invalid) and I want to correct all of them. I use the following SQL statement: 'SELECT [...] FROM MyTable WHERE WebAddress LIKE '_http%' and I successfuly get the problematic rows. But how am I going to change/correct all of them using an UPDATE statement? If you have some other solution please share it!

  Read the article

• CTE Join query issues

  - by Lee_McIntosh

  Hi everyone, this problem has me head going round in circles at the moment and i wondering if anyone could give any pointers as to where im going wrong. Im trying to produce a SPROC that produces a dataset to be called by SSRS for graphs spanning the last 6 months. The data for example purposes uses three tables (theres more but the it wont change the issue at hand) and are as follows: tbl_ReportList: Report Site ---------------- North abc North def East bbb East ccc East ddd South poa South pob South poc South pod West xyz tbl_TicketsRaisedThisMonth: Date Site Type NoOfTickets --------------------------------------------------------- 2010-07-01 00:00:00.000 abc Support 101 2010-07-01 00:00:00.000 abc Complaint 21 2010-07-01 00:00:00.000 def Support 6 ... 2010-12-01 00:00:00.000 abc Support 93 2010-12-01 00:00:00.000 xyz Support 5 tbl_FeedBackRequests: Date Site NoOfFeedBackR ---------------------------------------------------------------- 2010-07-01 00:00:00.000 abc 101 2010-07-01 00:00:00.000 def 11 ... 2010-12-01 00:00:00.000 abc 63 2010-12-01 00:00:00.000 xyz 4 I'm using CTE's to simplify the code, which is as follows: DECLARE @ReportName VarChar(200) SET @ReportName = 'North'; WITH TicketsRaisedThisMonth AS ( SELECT [Date], Site, SUM(NoOfTickets) AS NoOfTickets FROM tbl_TicketsRaisedThisMonth WHERE [Date] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0) GROUP BY [Date], Site ), FeedBackRequests AS ( SELECT [Date], Site, SUM(NoOfFeedBackR) AS NoOfFeedBackR FROM tbl_FeedBackRequests WHERE [Date] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0) GROUP BY [Date], Site ), SELECT trtm.[Date] SUM(trtm.NoOfTickets) AS NoOfTickets, SUM(fbr.NoOfFeedBackR) AS NoOfFeedBackR, FROM Reports rpts LEFT OUTER JOIN TotalIncidentsDuringMonth trtm ON rpts.Site = trtm.Site LEFT OUTER JOIN LoggedComplaints fbr ON rpts.Site = fbr.Site WHERE rpts.report = @ReportName GROUP BY trtm.[Date] And the output when the sproc is pass a parameter such as 'North' to be as follows: Date NoOfTickets NoOfFeedBackR ----------------------------------------------------------------------------------- 2010-07-01 00:00:00.000 128 112 2010-08-01 00:00:00.000 <data for that month> <data for that month> 2010-09-01 00:00:00.000 <data for that month> <data for that month> 2010-10-01 00:00:00.000 <data for that month> <data for that month> 2010-11-01 00:00:00.000 <data for that month> <data for that month> 2010-12-01 00:00:00.000 122 63 The issue I'm having is that when i execute the query I'm given a repeated list of values of each month, such as 128 will repeat 6 times then another value for the next months value repeated 6 times, etc. argh!

  Read the article

• t-sql get variable value from string with variable name

  - by Markus

  Hi. Is there a way to convert '@my_variable' string into a value of @my_variable? I have a table which stores names of variables. I need to get the value of this variable. Something like this: DECLARE @second_variable AS NVARCHAR(20); DECLARE @first_variable AS NVARCHAR(20); SET @first_variable = '20'; SET @second_variable = SELECT '@first_variable'; --here I want that @second variable be assigned a value of "20".

  Read the article

• insert into several inheritance tables with OUTPUT - sql servr 2005

  - by csetzkorn

  Hi, I have a bunch of items – for simplicity reasons – a flat table with unique names seeded via bulk insert: create table #items ( ItemName NVARCHAR(255) ) The database has this structure: create table Statements ( Id INT IDENTITY NOT NULL, Version INT not null, FurtherDetails varchar(max) null, ProposalDateTime DATETIME null, UpdateDateTime DATETIME null, ProposerFk INT null, UpdaterFk INT null, primary key (Id) ) create table Item ( StatementFk INT not null, ItemName NVARCHAR(255) null, primary key (StatementFk) ) Here Item is a child of Statement (inheritance). I would like to insert items in #items using a set based approach (avoiding triggers and loops). Can this be achieved with OUTPUT in my scenario. A ‘loop based’ approach is just too slow where I use something like this: insert into Statements (Version, FurtherDetails, ProposalDateTime, UpdateDateTime, ProposerFk, UpdaterFk) VALUES (1, null, getdate(), getdate(), @user_id, @user_id) etc. This is a start for the OUTPUT based approach – but I am not sure whether this would work in my case as ItemName is only inserted into Item: insert into Statements ( Version, FurtherDetails, ProposalDateTime, UpdateDateTime, ProposerFk, UpdaterFk ) output inserted.Id ... ??? Thanks. Best wishes, Christian

  Read the article

• How to run an SQL stored procedure through C# at a specific time?

  - by yishai neeman

  I am building a website now for a library and i need to check every day at the same time if there are people who need to return their books in the next five days and to send them a reminder via email. My question is what will be the correct way to do that? What i need to accomplish is when the specific time of day comes i need to run an sql stored procedure and check either through visual studio 2010 or any other way if the stored procedure has returned any results to which i need to email. Is there a way to maybe check the system time constantly on C# and not as a triggered event?

  Read the article

• Adding Days To Date in SQL

  - by Coding Noob

  I am trying to get data from my Database of those who have upcoming birth days in next few days(declared earlier) it's working fine for days but this query will not work if i add 24 days to current date cause than it will need change in month. i wonder how can i do it declare @date int=10, @month int=0 select * from STUDENT_INFO where DATEPART(DD,STDNT_DOB) between DATEPART(DD,GETDATE()) and DATEPART(DD,DATEADD(DD,@date,GETDATE())) and DATEPART(MM,STDNT_DOB) = DATEPART(MM,DATEADD(MM,@month,GETDATE())) This query works fine but it only checks date between 8 & 18 but if i use it like this declare @date int=30, @month int=0 select * from STUDENT_INFO where DATEPART(DD,STDNT_DOB) between DATEPART(DD,GETDATE()) and DATEPART(DD,DATEADD(DD,@date,GETDATE())) and DATEPART(MM,STDNT_DOB) = DATEPART(MM,DATEADD(MM,@month,GETDATE())) it will return nothing since it require addition in month as well If I Use it like this declare @date int=40, @month int=0 select * from STUDENT_INFO where DATEPART(DD,STDNT_DOB) between DATEPART(DD,GETDATE()) and DATEADD(DD,@date,GETDATE()) and DATEPART(MM,STDNT_DOB) = DATEPART(MM,DATEADD(MM,@month,GETDATE())) than it will return results till the last of this month but will not show till 18/12 which was required

  Read the article

• Tracing or Logging Resource Governor classification function behavior in Sql Server 2008

  - by nganju

  I'm trying to use the Resource Governor in SQL Server 2008 but I find it hard to debug the classification function and figure out what the input variables will have, i.e. does SUSER_NAME() contain the domain name? What does the APP_NAME() string look like? It's also hard to verify that it's working correctly. What group did the function return? The only way I can see this is to fire up the performance monitor and watch unblinkingly for little blips in the right CPU counter. Is there some way I can either run it in Debug mode, where I can set a breakpoint and step through and look at variable values, or can I at least do the old-school method of writing trace statements to a file so I can see what's going on? Thanks...

  Read the article

• How to use group by for grouping varchar data.

  - by Shantanu Gupta

  I have a table that contains some data given below pk_map_id preferences ImmediateParent Department_Id -------------------- -------------------- -------------------- -------------------- 20 14 5 1 21 15 5 1 22 16 6 1 23 9 4 2 24 4 3 2 25 24 20 2 26 25 20 2 27 23 13 2 I want to group my records on behalf of department then immediate parent then preferences each seperated by ',' i.e. department Immediate Parent preferences 1 5,6 14,15,16 2 4,3,20,13 9,4,24,25,23 and this table also Immediate parent preferences 5 14,15 6 16 4 9 3 4 20 24,25 13 13 In actual scenario all these are my ids which are to be replaced by their string fields. I am using sql server 2k5

  Read the article

• Restoring dev db from production: Running a set of SQL scripts based on a list stored in a table?

  - by mattley

  I need to restore a backup from a production database and then automatically reapply SQL scripts (e.g. ALTER TABLE, INSERT, etc) to bring that db schema back to what was under development. There will be lots of scripts, from a handful of different developers. They won't all be in the same directory. My current plan is to list the scripts with the full filesystem path in table in a psuedo-system database. Then create a stored procedure in this database which will first run RESTORE DATABASE and then run a cursor over the list of scripts, creating a command string for SQLCMD for each script, and then executing that SQLCMD string for each script using xp_cmdshell. The sequence of cursor-sqlstring-xp_cmdshell-sqlcmd feels clumsy to me. Also, it requires turning on xp_cmdshell. I can't be the only one who has done something like this. Is there a cleaner way to run a set of scripts that are scattered around the filesystem on the server? Especially, a way that doesn't require xp_cmdshell?

  Read the article

• Aggregate path counts using HierarchyID

  - by austincav

  Business problem - understand process fallout using analytics data. Here is what we have done so far: Build a dictionary table with every possible process step Find each process "start" Find the last step for each start Join dictionary table to last step to find path to final step In the final report output we end up with a list of paths for each start to each final step: User Fallout Step HierarchyID.ToString() A 1/1/1 B 1/1/1/1/1 C 1/1/1/1 D 1/1/1 E 1/1 What this means is that five users (A-E) started the process. Assume only User B finished, the other four did not. Since this is a simple example (without branching) we want the output to look as follows: Step Unique Users 1 5 2 5 3 4 4 2 5 1 The easiest solution I could think of is to take each hierarchyID.ToString(), parse that out into a set of subpaths, JOIN back to the dictionary table, and output using GROUP BY. Given the volume of data, I'd like to use the built-in HierarchyID functions, e.g. IsAncestorOf. Any ideas or thoughts how I could write this? Maybe a recursive CTE?

  Read the article

• Stored Proc : Use Return or Select at end?

  - by Mike

  If I'm creating a C# wrapper for a stored proc, and that sp only returns/selects (not sure) 1 value, should I use return or select at the end of that sp? This is for t-sql.

  Read the article

• How to find the worst performing queries in MS SQL Server 2008?

  - by Thomas Bratt

  How to find the worst performing queries in MS SQL Server 2008? I found the following example but it does not seem to work: SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id ORDER BY max_logical_reads DESC Taken from: http://www.sqlservercurry.com/2010/03/top-5-costly-stored-procedures-in-sql.html

  Read the article

• Query takes time on comparing non numeric data of two tables, how to optimize it?

  - by Muhammad Kashif Nadeem

  I have two DBs. The 1st db has CallsRecords table and 2nd db has Contacts table, both are on SQL Server 2005. Below is the sample of two tables. Contact table has 1,50,000 records CallsRecords has 75,000 records Indexes on CallsRecords: CallFrom CallTo PickUP Indexes on Contacts: PhoneNumber I am using this query to find matches but it take more than 7 minutes. SELECT * FROM CallsRecords r INNER JOIN Contact c ON r.CallFrom = c.PhoneNumber OR r.CallTo = c.PhoneNumber OR r.PickUp = c.PhoneNumber In Estimated execution plan inner join cost 95% Any help to optimize it.

  Read the article

• I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one?

  - by Cory Larson

  Hi all-- We're upgrading from SQL Server 2005 to 2008. Almost every database in the 2005 instance is set to 2000 compatibility mode, but we're jumping to 2008. Our testing is complete, but what we've learned is that we need to get faster at it. I've discovered some stored procedures that either SELECT data from missing tables or try to ORDER BY columns that don't exist. Wrapping the SQL to create the procedures in SET PARSEONLY ON and trapping errors in a try/catch only catches the invalid columns in the ORDER BYs. It does not find the error with the procedure selecting data from the missing table. SSMS 2008's intellisense, however, DOES find the issue, but I can still go ahead and successfully run the ALTER script for the procedure without it complaining. So, why can I even get away with creating a procedure that fails when it runs? Are there any tools out there that can do better than what I've tried? The first tool I found wasn't very useful: DbValidator from CodeProject, but it finds fewer problems than this script I found on SqlServerCentral, which found the invalid column references. ------------------------------------------------------------------------- -- Check Syntax of Database Objects -- Copyrighted work. Free to use as a tool to check your own code or in -- any software not sold. All other uses require written permission. ------------------------------------------------------------------------- -- Turn on ParseOnly so that we don't actually execute anything. SET PARSEONLY ON GO -- Create a table to iterate through declare @ObjectList table (ID_NUM int NOT NULL IDENTITY (1, 1), OBJ_NAME varchar(255), OBJ_TYPE char(2)) -- Get a list of most of the scriptable objects in the DB. insert into @ObjectList (OBJ_NAME, OBJ_TYPE) SELECT name, type FROM sysobjects WHERE type in ('P', 'FN', 'IF', 'TF', 'TR', 'V') order by type, name -- Var to hold the SQL that we will be syntax checking declare @SQLToCheckSyntaxFor varchar(max) -- Var to hold the name of the object we are currently checking declare @ObjectName varchar(255) -- Var to hold the type of the object we are currently checking declare @ObjectType char(2) -- Var to indicate our current location in iterating through the list of objects declare @IDNum int -- Var to indicate the max number of objects we need to iterate through declare @MaxIDNum int -- Set the inital value and max value select @IDNum = Min(ID_NUM), @MaxIDNum = Max(ID_NUM) from @ObjectList -- Begin iteration while @IDNum <= @MaxIDNum begin -- Load per iteration values here select @ObjectName = OBJ_NAME, @ObjectType = OBJ_TYPE from @ObjectList where ID_NUM = @IDNum -- Get the text of the db Object (ie create script for the sproc) SELECT @SQLToCheckSyntaxFor = OBJECT_DEFINITION(OBJECT_ID(@ObjectName, @ObjectType)) begin try -- Run the create script (remember that PARSEONLY has been turned on) EXECUTE(@SQLToCheckSyntaxFor) end try begin catch -- See if the object name is the same in the script and the catalog (kind of a special error) if (ERROR_PROCEDURE() <> @ObjectName) begin print 'Error in ' + @ObjectName print ' The Name in the script is ' + ERROR_PROCEDURE()+ '. (They don''t match)' end -- If the error is just that this already exists then we don't want to report that. else if (ERROR_MESSAGE() <> 'There is already an object named ''' + ERROR_PROCEDURE() + ''' in the database.') begin -- Report the error that we got. print 'Error in ' + ERROR_PROCEDURE() print ' ERROR TEXT: ' + ERROR_MESSAGE() end end catch -- Setup to iterate to the next item in the table select @IDNum = case when Min(ID_NUM) is NULL then @IDNum + 1 else Min(ID_NUM) end from @ObjectList where ID_NUM > @IDNum end -- Turn the ParseOnly back off. SET PARSEONLY OFF GO Any suggestions?

  Read the article

• How does one SELECT block another?

  - by Krip

  I'm looking at output of SP_WhoIsActive on SQL Server 2005, and it's telling me one session is blocking another - fine. However they both are running a SELECT. How does one SELECT block another? Shouldn't they both be acquiring shared locks (which are compatible with one another)? Some more details: Neither session has an open transaction count - so they are stand-alone. The queries join a view with a table. They are complex queries which join lots of tables and results in 10,000 or so reads. Any insight much appreciated.

  Read the article

• Use where condition with unjoined table

  - by Dragos D

  I have the following problem: I am trying to retreive a sum value based on one condition. Problem is that I can't join the conditional value since my tables aren't joined. Here is my syntax: SELECT SUM(value) FROM value v join day d on v.day_id = d.day_id join budget b on d.budget_id = b.budget_id join unit u on v.unit_id = u.unit_id where #output.class = (SELECT distinct s.class FROM sale s where s.id = #output.sale_id) AND u.unit_name in ('electronics') With this sybtax I get no error but neither values. Can you please tell me where I do wrong?

  Read the article

• Why is variable declared inside IF statement created even when condition evaluates to false?

  - by AspOnMyNet

  Since @A is never declared, sql server should throw an error, but it doesn’t. Why is that? DECLARE @i int = 1; IF @i > 10 BEGIN DECLARE @A int = 100; END PRINT @A; // doesn't return any result thanx

  Read the article

• T-SQL Better way to determine max of date (accounting for nulls)

  - by Josh

  I am comparing two dates and trying to determine the max of the two dates. A null date would be considered less than a valid date. I am using the following case statement, which works - but feels very inefficient and clunky. Is there a better way? update @TEMP_EARNED set nextearn = case when lastoccurrence is null and lastearned is null then null when lastoccurrence is null then lastearned when lastearned is null then lastoccurrence when lastoccurrence > lastearned then lastoccurrence else lastearned end; (This is in MS SQL 2000, FYI.)

  Read the article

• What are the differences between these?

  - by Amit Ranjan

  What are the differences between the two queries? SELECT CountryMaster.Id FROM Districts INNER JOIN CountryMaster ON Districts.CountryId = CountryMaster.Id SELECT CountryMaster.Id FROM CountryMaster INNER JOIN Districts ON Districts.CountryId = CountryMaster.Id Please mind the i) Table positions and second ii) On Fields As I know, output will be same. But I want to know, is there any drastic effects of the same if I neglect positions of tables and columns in complex queries or tables having tons of data like thousands and lakhs of rows...

  Read the article

• All possible combinations for two column data

  - by Alec Dobbie

  I have a two column view Product Id Tag ---------------------- 1 Leather 1 Watch 2 Red 2 Necklace 2 Pearl I'm trying to get all possible combinations of tags for a product as such: 1 Leather 1 Leather,Watch 2 Pearl 2 Pearl,Necklace 2 Pearl Necklace,Red 2 Necklace 2 Necklace, Red 2 Red I've found and stolen some SQL that give me the complete list for all but not the small versions, its below. Any ideas, it's started to make my head hurt. A virtual pint for the best answer. SELECT ProductId, (SELECT CAST(Tag + ', ' AS VARCHAR(MAX)) FROM ProductByTagView WHERE Product.ProductId = ProductByTagView.ProductId order by tag FOR XML PATH ('')) AS Tags FROM Product

  Read the article

• What's an efficient way to convert month and year string to a date?

  - by adam0101

  I have column with varchar values like "022008" that I need to convert into a datetime like "02/01/2008". This will run on millions of records for a conversion process. What is an efficient select statement for doing this?

  Read the article

• SQL Script to clear database tables.

  - by pencilslate

  I have a need to take backup of a SQL Server Db with tons of data in it and import into another environment for updating and testing. Since, i am not interested in the data, i just want to recreate the schema on my other server. There is an option called 'Generate Script', but is throwing errors running them on the target server. Curious, if anyone attempted to write a SQL script that would run through all the tables in the db and clear the rows, thereby i could just create the schema backup as .bak file and restore it into another server.

  Read the article

• Committing Transaction

  - by Costa

  Hi http://msdn.microsoft.com/en-us/library/ms189797.aspx In this link they are committing a transaction within catch clause IF (XACT_STATE()) = 1, I don't get it, if there is an error why they are committing it? even if the problem in select statement and there is no big deal committing it, why don't just roll it back. Thanks

  Read the article

• SQL Server error handling: exceptions and the database-client contract

  - by gbn

  We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel. Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods. Some our client developers don’t like SQL exceptions. They understand them in their languages but they don’t appreciate that the SQL is limited in how we can communicate issues. I don’t just mean SQL errors, such as trying to insert “bob” into a int column. I also mean exceptions such as telling them that a reference value is wrong, or that data has already changed, or they can’t do this because his aggregate is not zero. They’d don’t really have any concrete alternatives: they’ve mentioned that we should output parameters, but we assume an exception means “processing stopped/rolled back. How do folks here handle the database-client contract? Either generally or where there is separation between the DB and client code monkeys. Edits: we use SQL Server 2005 TRY/CATCH exclusively we log all errors after the rollback to an exception table already we're concerned that some of our clients won't check output paramaters and assume everything is OK. We need errors flagged up for support to look at. everything is an exception... the clients are expected to do some message parsing to separate information vs errors. To separate our exceptions from DB engine and calling errors, they should use the error number (ours are all 50,000 of course)

  Read the article

< Previous Page | 27 28 29 30 31 32 33 34 35 36 37 38  | Next Page >