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 20/49 | < Previous Page | 16 17 18 19 20 21 22 23 24 25 26 27  | Next Page >

  • Create a SQL query to retrieve most recent records

    - by mattruma
    I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows: Date User Status Notes ------------------------------------------------------- 1/8/2009 12:00pm B.Sisko In Out to lunch 1/8/2009 8:00am B.Sisko In 1/7/2009 5:00pm B.Sisko In 1/7/2009 8:00am B.Sisko In 1/7/2009 8:00am K.Janeway In 1/5/2009 8:00am K.Janeway In 1/1/2009 8:00am J.Picard Out Vacation I would like to query the data and return the most recent status for each user, in this case, my query would return the following results: Date User Status Notes ------------------------------------------------------- 1/8/2009 12:00pm B.Sisko In Out to lunch 1/7/2009 8:00am K.Janeway In 1/1/2009 8:00am J.Picard Out Vacation I am try to figure out the TRANSACT-SQL to make this happen? Any help would be appreciated.

    Read the article

  • MS Sql Get Top xx Blog Record From Umbraco by parameter...

    - by ccppjava
    Following SQL get what I need: SELECT TOP (50) [nodeId] FROM [dbo].[cmsContentXml] WHERE [xml] like '%creatorID="29"%' AND [xml] like '%nodeType="1086"%' ORDER BY [nodeId] DESC I need to pass in the numbers as parameters, so I have follows: exec sp_executesql N'SELECT TOP (@max) [nodeId] FROM [dbo].[cmsContentXml] WHERE [xml] like ''%creatorID="@creatorID"%'' AND [xml] like ''%nodeType="@nodeType"%'' ORDER BY [nodeId] DESC',N'@max int,@creatorID int,@nodeType int',@max=50,@creatorID=29,@nodeType=1086 which however, returns no record, any idea?

    Read the article

  • Fully automated SQL Server Restore

    - by hasen j
    I'm not very fluent with SQL Server commands. I need a script to restore a database from a .bak file and move the logical_data and logical_log files to a specific path. I can do: restore filelistonly from disk='D:\backups\my_backup.bak' This will give me a result set with a column LogicalName, next I need to use the logical names from the result set in the restore command: restore database my_db_name from disk='d:\backups\my_backups.bak' with file=1, move 'logical_data_file' to 'd:\data\mydb.mdf', move 'logical_log_file' to 'd:\data\mylog.ldf' How do I capture the logical names from the first result set into variables that can be supplied to the "move" command? I think the solution might be trivial, but I'm pretty new to SQL Server.

    Read the article

  • SQL Get Top 10 records by date

    - by Pselus
    I have a table full of bugs. The BugTitle is the page erroring and I also capture the error line. I would like to build an SQL Query that selects the top 10 bugs based on bugtitle and error line. I have this query: SELECT COUNT(BugTitle) AS BugCount, BugTitle, ErrLine FROM Bugs WHERE BugDate >= DateAdd(Day, -30, DateDiff(Day, 0, GetDate())) GROUP BY BugTitle, ErrLine ORDER BY BugCount, ErrLine DESC But I'm not sure if it's correct. I'm pretty sure that my test data only has 1 bug that happens on the same line but that's not showing up with this query. Can anyone help?

    Read the article

  • Help on understanding multiple columns on an index?

    - by Xaisoft
    Assume I have a table called "table" and I have 3 columns, a, b, and c. What does it mean to have a non-clustered index on columns a,b? Is a nonclustered index on columns a,b the same as a nonclustered index on columns b,a? (Note the order). Also, Is a nonclustered index on column a the same as a nonclustered index on a,c? I was looking at the website sqlserver performance and they had these dmv scripts where it would tell you if you had overlapping indexes and I believe it was saying that having an index on a is the same as a,b, so it is redundant. Is this true about indexes? One last question is why is the clustered index put on the primary key. Most of the time the primary key is not queried against, so shouldn't the clustered index be on the most queried column. I am probably missing something here like having it on the primary key speeds up joins? Great explanations. Should I turn this into a wiki and change the title index explanation?

    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

  • How to split a single column values to multiple column values?

    - by Shahsra
    Hi All, I have a problem splitting single column values to multiple column values. For Example: **Name** abcd efgh ijk lmn opq asd j. asdjja asb (asdfas) asd asd and I need the output something like this: first_name last_name abcd efgh ijk opq asd asdjja asb asd asd null The middle name can be omitted (no need for a middle name) The columns are already created and need to insert the data from that single 'Name' column. Thanks a lot, Shahsra

    Read the article

  • SQL Server (2005) Linked Server Issue

    - by David.Chu.ca
    I have SQL Server 2005 with several linked server defined. One of them is a connection to an Oracle server and another one is an ODBC bridge to another server on a remote machine (ODBC server). Recently I tried to use the linked server to Oracle to update data with two large size tables by using several joints. The update query took too long time and finally there was exception thrown: Update O set value = l.value FROM OracleServer..schema.largesizeTable O Join localLargeSizeTable l on .... The problem is that after the exception, I realized that another linked server to ODBC was not working any more. I had to restart SQL server to get the ODBC linked server back. It looks that the linked server pool could be crashed if any of them failed(not like sandbox in Chrome for each tab and no impact on other tabs or Chrome application at all). I am not sure if my assumption is correct or not. Is this a known issue of SQL server 2005?

    Read the article

  • Protocol error in TDS stream

    - by jasonline
    What are the possible causes of this type of error - "[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream"? And how can this be fixed? My application runs in C++ calling stored procedures implemented in SQL Server. There's this one SP (containing select and update statements) that causes this problem, after which all succeeding queries will return that the cursor is in an invalid state. I've checked the SP but I don't see any possible problem.

    Read the article

  • Deleting objects through foreign key relationship with T-SQL query

    - by LaserBeak
    Looking for a way to write the following LinQ to entities query as a T-SQL statement. repository.ProductShells.Where(x => x.ShellMembers.Any(sm => sm.ProductID == pid)).ToList().ForEach(x => repository.ProductShells.Remove(x)); The below is obviously not correct but I need it to delete respective ProductShell object where any ShellMember contains a ProductID equal to the passed in variable pid. I would presume this would involve a join statement to get the relevant ShellMembers. repository.Database.ExecuteSqlCommand("FROM Shellmembers WHERE ProductID={0} DELETE FK_ProductShell", pid); I have cascade delete enabled for the FK_ShellMembers_ProductShells foreign key, so when I delete the ProductShell it will delete all the ShellMembers that are associated with it. I am going to pass this statement to System.Data.Entity Database.ExecuteSqlCommand method.

    Read the article

  • How to reference a sql server with a slash (\) in its name?

    - by Bill Paetzke
    Givens: One SQL Server is named: DevServerA Another is named: DevServerB\2K5 Problem: From DevServerA, how can I write a query that references DevServerB\2K5? I tried a sample, dummy query (running it from DevServerA): SELECT TOP 1 * FROM DevServerB\2K5.master.sys.tables And I get the error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '\.'. However, I know my syntax is almost correct, since the other way around works (running this query from DevServerB\2K5): SELECT TOP 1 * FROM DevServerA.master.sys.tables Please help me figure out how to reference DevServerB\2K5 from DevServerA. Thanks.

    Read the article

  • How to make awkward pivot of sql table in MS SQL Server 2005?

    - by Oliver
    I have to rotate a given table from an sql server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format? Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time. Given Data ID | Label | Numerator | Denominator | Ratio ---+-----------------+-------------+---------------+-------- 1 | LabelNameOne | 41 | 10 | 4,1 1 | LabelNameTwo | 0 | 0 | 0 1 | LabelNameThree | 21 | 10 | 2,1 1 | LabelNameFour | 15 | 10 | 1,5 2 | LabelNameOne | 19 | 19 | 1 2 | LabelNameTwo | 0 | 0 | 0 2 | LabelNameThree | 15 | 16 | 0,9375 2 | LabelNameFive | 19 | 19 | 1 2 | LabelNameSix | 17 | 17 | 1 3 | LabelNameOne | 12 | 12 | 1 3 | LabelNameTwo | 0 | 0 | 0 3 | LabelNameThree | 11 | 12 | 0,9167 3 | LabelNameFour | 12 | 12 | 1 3 | LabelNameSix | 0 | 1 | 0 Wanted result ID | ValueType | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix ---+-------------+--------------+--------------+----------------+---------------+---------------+-------------- 1 | Numerator | 41 | 0 | 21 | 15 | | 1 | Denominator | 10 | 0 | 10 | 10 | | 1 | Ratio | 4,1 | 0 | 2,1 | 1,5 | | 2 | Numerator | 19 | 0 | 15 | | 19 | 17 2 | Denominator | 19 | 0 | 16 | | 19 | 17 2 | Ratio | 1 | 0 | 0,9375 | | 1 | 1 3 | Numerator | 12 | 0 | 11 | 12 | | 0 3 | Denominator | 12 | 0 | 12 | 12 | | 1 3 | Ratio | 1 | 0 | 0,9167 | 1 | | 0

    Read the article

  • How to do regex HTML tag replace in MS SQL?

    - by timmerk
    I have a table in SQL Server 2005 with hundreds of rows with HTML content. Some of the content has HTML like: <span class=heading-2>Directions</span> where "Directions" changes depending on page name. I need to change all the <span class=heading-2> and </span> tags to <h2> and </h2> tags. I wrote this query to do content changes in the past, but it doesn't work for my current problem because of the ending HTML tag: Update ContentManager Set ContentManager.Content = replace(Cast(ContentManager.Content AS NVARCHAR(Max)), 'old text', 'new text') Does anyone know how I could accomplish the span to h2 replacing purely in T-SQL? Everything I found showed I would have to do CLR integration. Thanks!

    Read the article

  • t-sql most efficient row to column? crosstab for xml path, pivot

    - by ajberry
    I am looking for the most performant way to turn rows into columns. I have a requirement to output the contents of the db (not actual schema below, but concept is similar) in both fixed width and delimited formats. The below FOR XML PATH query gives me the result I want, but when dealing with anything other than small amounts of data, can take awhile. select orderid ,REPLACE(( SELECT ' ' + CAST(ProductId as varchar) FROM _details d WHERE d.OrderId = o.OrderId ORDER BY d.OrderId,d.DetailId FOR XML PATH('') ),'&#x20;','') as Products from _orders o I've looked at pivot but most of the examples I have found are aggregating information. I just want to combine the child rows and tack them onto the parent. I should also point out I don't need to deal with the column names either since the output of the child rows will either be a fixed width string or a delimited string. For example, given the following tables: OrderId CustomerId ----------- ----------- 1 1 2 2 3 3 DetailId OrderId ProductId ----------- ----------- ----------- 1 1 100 2 1 158 3 1 234 4 2 125 5 3 101 6 3 105 7 3 212 8 3 250 for an order I need to output: orderid Products ----------- ----------------------- 1 100 158 234 2 125 3 101 105 212 250 or orderid Products ----------- ----------------------- 1 100|158|234 2 125 3 101|105|212|250 Thoughts or suggestions? I am using SQL Server 2k5. Example Setup: create table _orders ( OrderId int identity(1,1) primary key nonclustered ,CustomerId int ) create table _details ( DetailId int identity(1,1) primary key nonclustered ,OrderId int ,ProductId int ) insert into _orders (CustomerId) select 1 union select 2 union select 3 insert into _details (OrderId,ProductId) select 1,100 union select 1,158 union select 1,234 union select 2,125 union select 3,105 union select 3,101 union select 3,212 union select 3,250 using FOR XML PATH: select orderid ,REPLACE(( SELECT ' ' + CAST(ProductId as varchar) FROM _details d WHERE d.OrderId = o.OrderId ORDER BY d.OrderId,d.DetailId FOR XML PATH('') ),'&#x20;','') as Products from _orders o which outputs what I want, however is very slow for large amounts of data. One of the child tables is over 2 million rows, pushing the processing time out to ~ 4 hours. orderid Products ----------- ----------------------- 1 100 158 234 2 125 3 101 105 212 250

    Read the article

  • T-SQL: Omit/Ignore repetitive data from a specific column

    - by Dsyfa
    Hi, For my question lets consider the following sample table data: ProductID    ProductName    Price   Category 1                Apple                 5.00       Fruits 2                Apple                 5.00       Food 3                Orange               3.00       Fruits 4                Banana                 2.00       Fruits I need a query which will result in the following data set: ProductID    ProductName    Price   Category 1                Apple                 5.00       Fruits 3                Orange               3.00       Fruits 4                Banana                 2.00       Fruits As you can see ProductID 2 has been omitted/ignored because Apple is already present in the result i.e. each product must appear only once irrespective of Category or Price. Thanks

    Read the article

  • INSERT INTO temporary table from sp_executsql

    - by gotqn
    Generally, I am bulding dynamic SQL statement that is executing using sp_executsql like this: EXEC sp_executesql @TempSQLStatement I need to insert the return result row set in something (table variable or temporary table), but I am getting the following error: Msg 208, Level 16, State 0, Line 1746 Invalid object name '#TempTable'. after executing this: INSERT INTO #TempTable EXEC sp_executesql @TempSQLStatement From what I have read, I believe the issue is caused because I am not specifying the columns of the temporary table, but I am not able to do this because the return columns count varies. I have read that I can use global temporary tables, but I have done this before and wonder is there an other way to do that.

    Read the article

  • How do I select the number of distinct days in a date range?

    - by isme
    I'm trying to use the T-SQL function DATEDIFF to select the number of distinct dates in a time period. The following query: SELECT DATEDIFF(DAY, '2012-01-01 01:23:45', '2012-01-02 01:23:45') selects 1, which is one less than I want. There are two distinct dates in the range: 2012-01-01 and 2012-01-02. It is not correct to add one to the result in the general case. The following query: SELECT DATEDIFF(DAY, '2012-01-01 00:00:00', '2012-01-02 00:00:00') selects 1, which is correct, because there is only one distinct date in the range. I'm sure there is a simple bit of arithmetic that I'm missing to calculate this. Can someone help me?

    Read the article

  • Two different definitions of database schema

    - by AspOnMyNet
    a) I found two definitions of schema: FIRST - A set of information that describes a table is known as a schema, and schemas are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any). SECOND - A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. I assume the two descriptions describe entirely different concepts, which just happen to use the same name? b) A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. If I understand the above definition correctly, then database schema is similar to a namespace, only difference being that we can assign access permissions to database schema, while same can’t be done with namespaces? thanx

    Read the article

  • Dynamically change my schema

    - by Kirk
    I am wondering if there is a way to change the schema that I am working in while inside Management Studio. For instance I may have a default schema of dbo. But there are times I may want to query objects in say the accounting schema. It would be nice if I could issue a command and make it so I no longer must include the accounting before tables and views. But the next time I go in, I will be back to default of dbo.

    Read the article

  • Counting consecutive items within MS SQL

    - by Greg
    Got a problem with a query I'm trying to write. I have a table that lists people that have been sent an email. There is a bit column named Active which is set to true if they have responded. But I need to count the number of consecutive emails the person has been inactive since either their first email or last active email. For example, this basic table shows one person has been sent 9 emails. They have been active within two of the emails (3 & 5). So their inactive count would be 4 as we are counting from email number 6 onwards. PersonID(int) EmailID(int) Active(bit) 1 1 0 1 2 0 1 3 1 1 4 0 1 5 1 1 6 0 1 7 0 1 8 0 1 9 0 Any pointers or help would be great. Regards Greg

    Read the article

  • How to count incrementally in SQL SERVER

    - by joe
    Hi Folks, I am assigned a problem which i am not sure how to write it in SQL SEVER (version 5/8) yet. Here is the simple query and its output: Select location, date_time, Item_sold From Product Location Date_time Item_sold VA 12/10/2010 1:30:00 PM Candy VA 12/10/2010 3:30:00 PM Chips VA 12/13/2010 12:50:00 AM Wine DC 12/13/2010 8:00:00 AM Gum DC 12/13/2010 12:30:00 PM Bags DC 12/13/2010 1:16:00 PM Cheese DC 12/13/2010 12:00:00 AM Hotdog NJ 12/15/2010 12:00:00 AM Coffee NJ 12/15/2010 1:15:00 PM Beers NJ 12/15/2010 3:45:00 AM Cream Here is my desired output, which I guess a while/for loop or a pivot function could do the job but my experience is not there yet. Basically, I need to count the number of item sold from the Item_sold column incrementally (base line date starts from 12/8 to 12/9, 12/8 to 12/10, 12/8 to 12/11, 12/8 to 12/12...) I was wondering if anyone could edcuate and solve this problem. Many thanks in advance, Joe Location 12/8 |12/8-12/9 |12/8-12/10 |12/8 - 12/11 |12/8 - 12/12 |12/8 - 12/13 VA 0 0 2 0 0 3 DC 0 0 0 0 0 4

    Read the article

  • T-SQL - Date rounding and normalization

    - by arun prakash
    Hi: I have a stored procedure that rounds a column with dates in (yyyy:mm:dd hh:mM:ss) to the nearest 10 minute handle (yyyy:mm:dd hh:mM) 20100303 09:46:3000 ------ 20100303 09:50 but i want to chage it to round it off to the nearest 15 minute handle: 20100303 09:46:3000 ------20100303 09:45 here is my code : IF OBJECT_ID(N'[dbo].[SPNormalizeAddWhen]') IS NOT NULL DROP PROCEDURE [dbo].[SPNormalizeAddWhen] GO CREATE PROCEDURE [dbo].[SPNormalizeAddWhen] As declare @colname nvarchar(20) set @colname='Normalized Add_When' if not exists (select * from syscolumns where id=object_id('Risk') and name=@colname) exec('alter table Risk add [' + @colname + '] datetime') declare @sql nvarchar(500) set @sql='update Risk set [' + @colname + ']=cast(DATEPART(yyyy,[add when]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[add when]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[add when]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[add when]) as nvarchar(2)) + '':'' + cast(round(DATEPART(Mi,[add when]),-1) as nvarchar(2)) ' print @sql exec(@sql) GO

    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 | 16 17 18 19 20 21 22 23 24 25 26 27  | Next Page >