Search Results

Search found 84007 results on 3361 pages for 'sql system table'.

Page 137/3361 | < Previous Page | 133 134 135 136 137 138 139 140 141 142 143 144  | Next Page >

  • T-SQL SQL Server - Stored Procedure with parameter

    - by Ricardo Conte
    Please, the first TSQL works FINE, the second does not. I guess it must be a simple mistake, since I am not used to T-SQL. Thank you for the answers. R Conte. * WORKS FINE ******************* (parm hard-coded) ALTER PROCEDURE rconte.spPesquisasPorStatus AS SET NOCOUNT ON SELECT pesId, RTRIM(pesNome), pesStatus, pesPesGrupoRespondente, pesPesQuestionario, pesDataPrevistaDisponivel, pesDataPrevistaEncerramento, pesDono FROM dbo.tblPesquisas WHERE (pesStatus = 'dis') ORDER BY pesId DESC RETURN Running [rconte].[spPesquisasPorStatus]. pesId Column1 pesStatus pesPesGrupoRespondente pesPesQuestionario pesDataPrevistaDisponivel pesDataPrevistaEncerramento pesDono 29 XXXXXXXXX xxxxx dis 17 28 5/5/2010 08:21:12 5/5/2010 08:21:12 1 28 Xxxxxxxx xxxxxxxxxxxxx dis 16 27 5/5/2010 07:44:12 5/5/2010 07:44:12 1 27 Xxxxxxxxxxxxxxxxxxxxxxx * DOES NOT WORK ************** (using a parm; pesStatus is nchar(3)) ALTER PROCEDURE rconte.spPesquisasPorStatus (@pPesStatus nchar(3) = 'dis') AS SET NOCOUNT ON SELECT pesId, RTRIM(pesNome), pesStatus, pesPesGrupoRespondente, pesPesQuestionario, pesDataPrevistaDisponivel, pesDataPrevistaEncerramento, pesDono FROM dbo.tblPesquisas WHERE (pesStatus = @pPesStatus) ORDER BY pesId DESC RETURN Running [rconte].[spPesquisasPorStatus] ( @pPesStatus = 'dis' ). pesId Column1 pesStatus pesPesGrupoRespondente pesPesQuestionario pesDataPrevistaDisponivel pesDataPrevistaEncerramento pesDono No rows affected. (0 row(s) returned) @RETURN_VALUE = 0 Finished running [rconte].[spPesquisasPorStatus]

    Read the article

  • Selecting fields in SQL Select statements (Dumbest SQL Question)

    - by JC
    Hello all, Here's a dumb question which I can't find an answer to: I have a table which contains 20 fields, a few of which are date/time. I am interested in pulling all the fields. I would like to pull the datetime fields using the to_char function but don't want to individually list out all the other fields. Is there an easy way to do this? I tried select *, tochar(dtfield) as dt2 and select tochar(dtfield) as dt2, * and both give errors. Thanks for all your help! JC

    Read the article

  • What does SQL Server trace flag 253 do?

    - by kamens
    In another question I was trying to research how to control SQL Server's query plan caches: http://stackoverflow.com/questions/2593749/is-there-an-equivalent-of-optionrecompile-or-with-recompile-for-an-entire-c ...and I found trace flag 253 via this article: http://www.sqlservercentral.com/Forums/Topic837613-146-1.aspx The article is correct, if I run DBCC TRACEON(253) and then a number of queries, I can manually check the query plan cache and see that plans have not been inserted. If I run DBCC TRACEOFF(253), query plans are cached as normal. So my question is...what else does this flag do? Does anybody know the official story?

    Read the article

  • Visual Studio error on valid SQL

    - by Sam
    I'm getting an annoying error in visual studio for SQL that executes fine. SELECT InvoiceLines.LineID, InvoiceLines.InvoiceID, InvoiceLines.Text, InvoiceLines.Rate, InvoiceLines.Count, InvoiceLines.Rate * InvoiceLines.Count AS LineTotal, ((InvoiceLines.Rate * InvoiceLines.Count) * (1 + Invoices.VatRate / 100)) * (1 - CAST(Invoices.Discount AS money) * InvoiceLines.ApplyDiscount / 100) AS LineTotalIncVat, InvoiceLines.ApplyDiscount FROM InvoiceLines LEFT JOIN Invoices ON Invoices.InvoiceID = InvoiceLines.InvoiceID What LineTotalIncVat is trying to do is compute the total for the invoice item while adding the vat and subtracting the discount, and yes, probably better to do this in code (would if I could) The error visual studio gives is: There was an error parsing the query [token line number =1, token line offset =14, token in error = InvoiceLines] Even though it will validate and execute without a problem in the query builder...

    Read the article

  • SQL Server Group Concat with Different characters

    - by Molloch
    I have looked through a number of solutions to emulating "Group concat" functionality in SQL Server. I wanted to make a more human readable solution though and I can't work out how to do it. I have a view: ParentID | ChildName Which contains the records, for example: 1 | Max 1 | Jessie 2 | Steven 2 | Lucy 2 | Jake 3 | Mark I want to "Group Concat" these to get: 1 | Max and Jessie 2 | Steven, Lucy and Jake 3 | Mark So If there is only 1 child, just return name, if there are more than one, concat the last 2 with an ' and ' and all others with a ', '. I am a bit stuck on how to do this without resorting to CLR, which I don't want to do. I am happy with a function - but speed is an issue and how do I determine the child number so I can choose between ' and ', ', ' or ''?

    Read the article

  • How to register System.DirectoryServices for use in SQL CLR User Functions?

    - by Saul Dolgin
    I am porting an old 32-bit COM component that was written in VB6 for the purpose of reading and writing to an Active Directory server. The new solution will be in C# and will use SQL CLR user functions. The assembly that I am trying to deploy to SQL Server contains a reference to System.DirectoryServices. The project does compile without any errors but I am unable to deploy the assembly to the SQL Server because of the following error: Error: Assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog. What are the correct steps for registering System.DirectoryServices on SQL Server?

    Read the article

  • I need a query designed for MySQL translated to work for SQL Server 2005

    - by brookmarker
    I've spent a whole day on this already without figuring it out. I'm hoping somebody can help me translate the following MySQL query to work for SQL Server 2005: SELECT MAX ( messages.date ) AS maxdate, topics.id AS topicid, topics.*, users.* FROM messages, topics, users WHERE messages.topic_id = topics.id AND topics.user_id = users.id AND topics.forum_id = " . $forumid . " GROUP BY messages.topic_id ORDER BY maxdate DESC $forumid is a QueryString value defined in the following VB.NET code-behind code: forumName.Text = "<a href='ViewForum.aspx?forumid=" & row.id & "'>" & row.name & "</a>" I'd be super grateful if u can help.

    Read the article

  • How to use correctly the Query Window in SQL Server 2008

    - by Richard77
    Hello, What should I do to avoid that commands be executed each time I hit 'Execute !. icon' I mean this USE master; GO CREATE DATABASE Sales GO USE Sales; GO CREATE TABLE Customers( CustomerID int NOT NULL, LName varchar (50) NOT NULL, FName varchar (50) NULL, Status varchar (10), ModifiedBy varchar (30) NULL ) GO When I click Execute!, Sql Server tries to redo the same thing. What I do for now is to delete the Query Window completely then write what I need before clicking the Execute icon. But, I doubt that I should be doing that. What can I do to keep writing the commands without having each time to clear the Query Window? Thanks for helping

    Read the article

  • Optimizing ROW_NUMBER() in SQL Server

    - by BlueRaja
    We have a number of machines which record data into a database at sporadic intervals. For each record, I'd like to obtain the time period between this recording and the previous recording. I can do this using ROW_NUMBER as follows: WITH TempTable AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Ordering FROM dbo.DataTable ) SELECT [Current].*, Previous.Date_Time AS PreviousDateTime FROM TempTable AS [Current] INNER JOIN TempTable AS Previous ON [Current].Machine_ID = Previous.Machine_ID AND Previous.Ordering = [Current].Ordering + 1 The problem is, it goes really slow (several minutes on a table with about 10k entries) - I tried creating separate indicies on Machine_ID and Date_Time, and a single joined-index, but nothing helps. Is there anyway to rewrite this query to go faster?

    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

  • 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

  • Exporting query results to a file on the fly

    - by ercan
    Hi all, I need to export the results of a query to a csv file in an FTP folder. Is it possible to achieve this within a stored procedure? If yes, comes yet another constraint: can I achieve this without sysadmin privileges, aka without using xp_cmdshell + BCP utility? If no to 2., does the caller have to have sysadmin privileges or would it suffice if the SP owner has sysadmin privileges? Here are some more details to the problem: The SP must export and transfer the file on the fly and raise error if something went wrong. The caller must get a response immediately, i.e. in case of no error, he can assume that the results are successfully transferred to the folder. Therefore, a DTS/SSIS job that runs every N minutes is not an option. I know the problem smells like I will have to do this at application level, but I would be more than happy if all those stuff could be done from T-SQL.

    Read the article

  • How to Prove that using subselect queries in SQL is killing performance of server

    - by adopilot
    One of my jobs it to maintain our database, usually we have troubles with lack of performance while getting reports and working whit that base. When I start looking at queries which our ERP sending to database I see a lot of totally needlessly subselect queries inside main queries. As I am not member of developers which is creator of program we using, they do not like much when I criticize they code and job. Let say they do not taking my review as serious statements. So I asking you few questions about subselect in SQL Does subselect is taking a lot of more time then left outer joins? Does exists any blog, article or anything where I subselect is recommended not to use ? How I can prove that if we avoid subselesct in query that query is going to be faster ? Our database server is MSSQL2005

    Read the article

  • SQL 2005: Select top N, group by ID with joins

    - by Suzy Fresh
    I'm having real difficulty with a query involving 3 tables. I need to get the 3 newest users per department grouped by department names. The groups should be sorted by the users.dateadded so the department with the newest activity is first. The users can exist in multiple departments so Im using a lookup table that just contains the userID and deptID. My tables are as follows. Department - depID|name Users - userID|name|dateadded DepUsers - depID|userID The output I need would be Receiving John Doe - 4/23/2010 Bill Smith - 4/22/2010 Accounting Steve Jones - 4/22/2010 John Doe - 4/21/2010 Auditing Steve Jones - 4/21/2010 Bill Smith - 4/21/2010

    Read the article

  • Converting SQL to LINQ to XML

    - by Morano88
    I'm writing the following code to convert SQL to LINQ and then to XML: SqlConnection thisConnection = new SqlConnection(@"Data Source=3BDALLAH-PC;Initial Catalog=XMLC;Integrated Security=True;Pooling=False;"); thisConnection.Open(); XElement eventsGive = new XElement("data", from c in ?????? select new XElement("event", new XAttribute("start", c.start), new XAttribute("end",c.eend), new XAttribute("title",c.title), new XAttribute("Color",c.Color), new XAttribute("link",c.link))); Console.WriteLine(eventsGive); The name of the table is "XMLC" and I want to refer to it. How can I do that? When I put its name directly VS gives an error. Also when I say thisConnection.XMLC it doesn't work.

    Read the article

  • Setting up a SQL server outside the domain

    - by Ben
    Hi, im very new to this. I've set up an account "SQLBOX" thats on the a network, but not connected to the domain. I have installed an instance of SQL Server 2008 and am trying to connect to it from another machine on the network but am getting "Cannot connect to "SQLBOX"". From the "SQLBOX" i can connect to sqlservers on the domain, but not vice-versa. Any help would be greatly appreciated. Sorry if the description isn't great. EDIT I can ping the box from a machine on the domain

    Read the article

  • MS SQL replace sequence of same characters inside Text Field (TSQL only)

    - by zmische
    I have a text column varchar(4000) with text: 'aaabbaaacbaaaccc' and I need to remove all duplicated chars - so only one from sequence left: 'abacbac' It should not be a function, Procedure or CLR - Regex solution. Only true SQL select. Currently I think about using recursive WITH clause with replace 'aa'-'a', 'bb'-'b', 'cc'-'c'. So recursion should cycle until all duplicated sequences of that chars would be replaced. DO you have another solution, perhaps more Permormant one? PS: I searched through this site about different replace examples - they didnt suit to this case.

    Read the article

  • Random Number on SQL without using NewID()

    - by Angel Escobedo
    Hello I want to generate a Unique Random number with out using the follow statement : Convert(int, (CHECKSUM(NEWID()))*100000) AS [ITEM] Cause when I use joins clauses on "from" it generates double registers by using NEWID() Im using SQL Server 2000 *PD : When I use Rand() it probably repeat on probability 1 of 100000000 but this is so criticall so it have to be 0% of probability to repeat a random value generated My Query with NewID() and result on SELECT statement is duplicated (x2) My QUery without NewID() and using Rand() on SELECT statement is single (x1) but the probability of repeat the random value generated is uncertainly but exists! Thanks!

    Read the article

  • SQL Server T-SQL statement to replace/delete sub-strings

    - by StefanE
    Hi, I have a table with 6 columns containing HTML content with some markups in it and now when moving to a new designed site most of this HTML code has to be deleted. More or less all tags except <B> and </B>. Is there a nice way of doing this, identify all tags end delete them within the data? I'm sure there are no < symbols in the test so a regular expression would maybe work? My alternative is to fetch every row, process it and update the database but I'm guessing this is possible to do in T-SQL directly. My server is an MSSQL 2008 and is located in a hosted environment but I can fetch a local copy if needed. Thanks, Stefan

    Read the article

  • What does xp_qv do in SQL Server?

    - by Chris
    Last night one of our SQL servers developed some major problems and after a colleague stopped, started, and all the usual things it started checking and rebuilding databases and is now running an extended stored procedure called "xp_qv". The internet seems to be very short of information on what this procedure does or anythign like that so I was hoping somebody here might be able to help. I should add that I assume it is meant to be running so the question isn't "Can I stop it" or anything like that, its just curiosity in what it is doing in the hope that it will help determine how long before things are usable again...

    Read the article

  • SQL Query Help Part 2 - Add filter to joined tables and get max value from filter

    - by Seth
    I asked this question on SO. However, I wish to extend it further. I would like to find the max value of the 'Reading' column only where the 'state' is of value 'XX' for example. So if I join the two tables, how do I get the row with max(Reading) value from the result set. Eg. SELECT s.*, g1.* FROM Schools AS s JOIN Grades AS g1 ON g1.id_schools = s.id WHERE s.state = 'SA' // how do I get row with max(Reading) column from this result set The table details are: Table1 = Schools Columns: id(PK), state(nvchar(100)), schoolname Table2 = Grades Columns: id(PK), id_schools(FK), Year, Reading, Writing...

    Read the article

  • Adjusting Timezone - Convert XML DateTime to SQL DateTime

    - by noob.spt
    We are using TypedDataSet in our application. Data is passed to procedure in form of XML for insert/update. Now after populating DE with data, datetime remains the same though timezone information is added as below. Date in DB: 2009-10-29 18:52:53.43 Date in XML: 2009-10-29T18:52:53.43-05:00 Now when I am trying to convert below XML to SQL DateTime it is adjusting 5 hours and I am getting 2009-10-29 23:52:53.430 as the final output, which is wrong. Need to find a way to extract datetime from below XML snippet ignoring timezone. I have XML in following format, with timezone difference -05.00 <Order> <EnteredDateTime>2009-10-29T18:52:53.43-05:00</EnteredDateTime> </Order>

    Read the article

  • SQL server - climb up in the tree structure

    - by Vytas999
    Hello. I have some sql table, named Object, which saves tree data in fields ObjectID, ParentID, and others. I have implemented recurse procedure, which select everything down by objectID from tree, like this: 1. 1.1. 1.2. 1.2.1. ... Now o need to "Climb up" - by some ObjectID i need to select everything Up, like this: 1.2.1. 1.2. 1. How i can do that? In example, my "down" procedure looks like: ALTER PROCEDURE [dbo].[Object_SelectDownByRoot_Simple] @ObjectID int AS WITH tree (ObjectID, ParentID, ObjectName, ObjectCode) AS ( SELECT ObjectID, ParentID, ObjectName, ObjectCode FROM dbo.[ObjectQ] ofs WHERE( ObjectID = @ObjectID ) UNION ALL SELECT ofs.ObjectID, ofs.ParentID, ofs.ObjectName, ofs.ObjectCode FROM dbo.[ObjectQ] ofs JOIN tree ON tree.ObjectID = ofs.ParentID ) SELECT ObjectID, ParentID, ObjectName, ObjectCode FROM tree

    Read the article

  • SQL Server Conditional Mailing Address Formatting

    - by Rob Packwood
    I have the following SQL to format a US address into each line for a mailing address but it is rather ugly. Is there a better way to solve this problem or does it have to be this ugly? declare @NL varchar(2); set @NL = char(13) + char(10); select case when rtrim(coalesce(AttentionLine,'') ) != '' then rtrim(AttentionLine ) + @NL else '' end + case when rtrim(coalesce(Recipient,'') ) != '' then rtrim(Recipient ) + @NL else '' end + case when rtrim(coalesce(AddlAddrLine,'') ) != '' then rtrim(AddlAddrLine ) + @NL else '' end + case when rtrim(coalesce(DeliveryAddr,'') ) != '' then rtrim(DeliveryAddr ) + @NL else '' end + case when rtrim(coalesce(LastLine,'') ) != '' then rtrim(LastLine ) + @NL else '' end + case when rtrim(coalesce(Country,'') ) != '' then rtrim(Country ) + @NL else '' end as FormattedMailingAddress from Address where Id = 1

    Read the article

  • SQL Server WHERE Clause using Temporary Columns

    - by AbeP
    Hi all, I have the following query, which uses a CASE statement. Is there anyway to add into the where clause WHERE IsBusinessDayFinal = 0? without using a temporary table? thanks so much! SELECT ac.DateTimeValue, CASE WHEN pc.IsBusinessDay IS NOT NULL THEN pc.IsBusinessDay ELSE ac.IsBusinessDay END AS IsBusinessDayFinal, ac.FullYear, ac.MonthValue, ac.DayOfMonth, ac.DayOfWeek, ac.Week FROM [dbo].[AdminCalendar] ac LEFT JOIN [dbo].ProjectCalendar pc ON ac.DateTimeValue = pc.DateTimeValue AND pc.ProjectId = @projectId WHERE ac.DateTimeValue >= @startDate AND ac.DateTimeValue <= @finishDate;

    Read the article

< Previous Page | 133 134 135 136 137 138 139 140 141 142 143 144  | Next Page >