hi all, Pls give me some tips how can i imporve performance my simple query,which point i rember when i write any query. thanks ...more >>

What new options do we have on SQL Server 2005 when exporting query output to an XML file (not using SSIS)? 1) sp_makewebtask -- small results only 2) bcp "EXEC Northwind..sproc_GetShippers" queryout c:\temp\bcpOut.xml -S. -Usa -P -c -r -t -- must append header and footer files C:\...more >>

Hi, How can I get all the hiredates which is falls in the previous month? Supposed today is November 9th. So I would like to take todays' date - GetDate() Now I want to find the previous month's hires. SELECT FirstName + ' ' + LastName AS Emp_Name, LastName, HireDate, MONTH (GETD...more >>

I have two tables. PRODUCT ProductId A B C D ORDER ProductId OrderDate A 2006-11-01 A 2006-10-01 A 2006-09-15 B 2006-10-15 ..... I want to see the 2 latest order date for each product in one row... ProductId OrderDate1 OrderDate2 ...more >>

I have some data like this: 708466 18/09/1940 709773 17/04/1944 712227 5/01/1951 706752 9/01/1936 721803 25/03/1977 708337 12/05/1940 718523 1/04/1968 716105 18/08/1961 707480 6/01/1938 710201 19/06/1945 707142 2/02/1937 713719 5/02/1955 The LEFT column of data is the 'stored' dat...more >>

Hey all, Currently I have an SQL extract which returns a variety of information using 3 views. Lets say a column in the output is REQUEST NUMBER. So I might have 99R12345 20R98765 20R98765 20R98765 What I need to do when I create the output is append a "D" to all the multiple/dupli...more >>

Hi all, i have a fairly complex SQL statement i should build. to make things easier i wrote some pseudocode that should clarify what the SQL does: setup: Table: TBMLCASHFLOW Important attributes: - CFID (the ID of the cashflow), - BDCONTEXTID (the ID of linked cash flows: cash flows that ...more >>

I would like to count and average multiple items in the query below. What I am trying to get is the number of sessions on verage per case. The session is defined as the claim_Detail_units and a case is defined as Claim_NewRecord. I have also posted a copy of the output below the query. SELECT...more >>

Hi, I am using the agent to perform my database daily backup and the backup file name is daily. I want to develop the script to change the file name with current date. but I don't how to do it, would anyone can help me? Thank you. Thomas...more >>

How would the SQL statement be coded for the following... I want to update the "Code" column in table2 (shown below) to the ID value found in table1 where field "optcode" in table 2 matches field optcode in table 1... (then I will eliminate the optcode field altogether from my db design) He...more >>

We have a table with 2 columns as primarykey. For example: companyid, orderid if we have the following data in the table: companyid orderid decription 1 1 aa 1 2 aa 1 3 bb 2 ...more >>

I have an update trigger (see beloe) that I would like to add a possible INSERT at the end (after the UPDATE) based on a condition of the value of the CheckedOut column before it is updated. How can I reference that value? Thanks. David ALTER TRIGGER T_FileMst_UTrig ON dbo.File_Mst ...more >>

I have sql 2005. i have a query where i want to return only first 10 characters of all character type data. what is the difference in execution speed or returned resulset of following go set textsize 10 select col1,col2,col3 from t1 go select substring(col1,1,10),substring(col2,1,10),subs...more >>

***SQL novice*** I have a table that looks like UserA 10 UserA 20 UserA 30 UserB 40 UserB 50 UserB 60 I can't see to find any references on how to calculate the median in this case. Most I find online address the calculation when done on the entire table (the median of UserA ...more >>

I need to take two queries, get a result then see if their results have the same data in them... kinda like this Query A returns (1,45,65) Query B returns (1,45,65) Query C returns (5,43,12) I need to compare query A to B and see if their result is the same set, how would I go about doin...more >>

Is there a Query I can run that will check all of my linked server, and tell me if the server on the other end is on line or not? I have a Query that every 5 minutes makes a call to a table on a bunch of linked server. Occasionally one of those servers goes down, and this causes the query to f...more >>

I am trying to build templates in VS2005, but do not understand the text in the URL below. Any practice with the present text? Or other places I can read more? http://msdn.microsoft.com/msdnmag/issues/06/01/CodeTemplates/default.aspx "Choosing this option allows you to search for starter...more >>

Just getting used to dynamic SQL here and now wondering if I'm doing something wrong. I thought you're supposed to use the same-named variable for both the parameter definition list and the parameter values list: EXEC sp_executesql @InsertString, N'@prefix CHAR(3), @policy INT, @write_c...more >>

Replacing text in query As an example I have the query below where the field pr_info has the data type TEXT. It means that it contain text with multiple lines. When using this against a Web application I need the line feed to be replaced by the HTML tag for line feed <br> . Is there a s...more >>

I'm working at gaining the ability to debug stored procedures on our SQL Server 2000 database. According to our support person, everything should be in place to allow debugging. However it would appear that there is still some piece missing somewhere. All developers are working on Win XP ...more >>

I am very very new to SQL server. We have SQL server 2000 and am working on moving an access db over to SQL server. In the process I am making table/relationship changes etc..etc... because the application using this DB is also being completely re-written in vb.net. Here is my problem. ...more >>

Hi, I am using ASP with SQL Server200. I am getting data from three tables then showing result. Its performance is not good. I want to use store procedure for this. IWhat I am doing in ASP SQL = "Select Custid, BranchId, purchord, PCode, description, " & _ deliverydate, itemgroup, it...more >>

Columns defined with an 'identity' data type allow you to retrieve the server generated value when performing an insert by using the @@identity command. With columns defined as 'uniqueidentifier' and the default value of newid() is there a similar option to retrieve the server generated val...more >>

Hi... We've got some apps sharing some tables, feeding a lucene index. Some sprocs add/update rows, other apps read the data to build/maintain the lucene index. The guys writing the add/update sprocs were complaining that, given the time Lucene was taking to do all its indexing and the lo...more >>

hi, I have an email field and I want to get a list of email and save to a space or semicolon delimit format, how to do that? thanks. ...more >>

Hello, I need some help on speeding up my procedure, its taking way to long to run. Any suggestions? ALTER PROCEDURE dbo.IGoogleSitemapUrlSet ( @SiteID Int, @Index Int = 0, @Size Int = 50000 ) AS SET NOCOUNT ON DECLARE @StartTime datetime DECLARE @LowerBound int DECLARE @Upp...more >>

Hello, A vendor wants me to turn off the truncate log on checkpoint option on a particular DB. Checked in BOL and can't find it. Can anyone advise? When I click on the DB and right click to see Properties, it hangs and doesn't do anything. Thanks, Pancho...more >>

Hello All, Is there any way in SQL to determine if a field contains double byte characters? Thanks, Walter ...more >>

Hello all: Trying my hand at an analysis services project, but the Deploy fails with the error: "The following system error occurred: Logon failure: unknown user name or bad password." Okey-dokey, says I; I pull up the datasource editor, go to the "Impersonation Information" tab, ...more >>

I have to write some sql that has to create a table from which a report runs that spans over the end of this year. It's code to build a table that a sales program report is generated from. The program itself runs until the end of January. From now thru January 2, orders in the program co...more >>

Suppose we have a datetime picker control in a web form. As we are working in ASP, We have used a Java Script function for capturing the date. The problem we are facing is that when we don't select any date i.e. we provide null data to the textbox , a value of "01 Jan 1900" gets inserted upon ...more >>

This might be a tough question. I would like to know if sql 2005 server performs better on 2 Dual-core or 4 Processor cpus? To my understanding, Dual core is more affordable and also offer the same or better performance. I think 2 dual-core is a better choice than 4 processors, but I just ...more >>

Can someone please help me to understand what that means: Error: Connectivity error: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionTransact (GetOverLappedResult()). SQL Command: usp_UpdateReading 'U1519 ', 4, 3 ...more >>

On Friday (11/3) it was determined that one of the standard coding practices utilized in Cage & Table Accounting (CTA) has a problem inside the MS SQL engine (MS SQL 2000 SP3). I was wondering if you had seen anything like this before or if Microsoft has it identified and/or fixed. ...more >>

Hi I am a relative newbie to SQL, and have a question about directed graphs. I want to design a schema for a 'hierarchy' in which a child has more than one parent. I know that this is technically not a hierarchy, but a form of the more general graph problem. My problem is: -Nodes may have more...more >>

Hello - I'm attempting to extract only certain values from a string of values in a field. For example, I would like to pull the code 3K and display in my results in one column and FP in another column. Currently both are stored in the same column in the database amongst other codes. So ...more >>

"With MS SQL Server, triggers fire once per statement regardless of the number of rows affected. The deleted and inserted tables within the update trigger will contain the before and after images of the updated rows. " Is there a way to determine which of the rows in inserted belongs to whi...more >>

Long story short: small company, 1 technical called upon to perform several roles. I've created a web-site allowing users to edit/insert/delete records. There are 8 tables involved here. The DB is relational. I have the task now of creating a site that allows admins to view the entire history of...more >>

Hi. I have created a linked server to an access database in SQL 7, and added a login for admin. When I use the linked server on the local machine, everything works fine. I am using version 2.82.1830.0 of MDAC on the server. When I attempt to use it through a remote connection, I get the...more >>

Does it matter if you have a really wide non-unique clustered index on a table being as it's just the order the data is stored in, or only if you have other indexes which point to the wide one? Thanks ...more >>

Hi we have some blocks I am using the microsoft script sp_blocker_pss80 and all the the lastwaittype are LATCH_EX and the waitresoursce is MISC (80228AA8). Does anyboby have a clue what these mean as I cannot find any information anywhere thanks for any help Sammy ...more >>

I need some feedback on a tricky subject: Given: CREATE TABLE [TEST] ( [id] [bigint] IDENTITY (1, 1) NOT NULL , [id2] [bigint] NULL CONSTRAINT [DF__TEST__id2__7C104AB9] DEFAULT (dbo.IDENT('test')) [field] varchar NULL ) ON [PRIMARY] Given the following function CRE...more >>

Hi, I'm using sql server 2005 SP1. I have a deployment system for my application tier that is built on auto builds and NAnt and suchlike. It would be good to be able to deploy the database objects that are used with the application at the same time. My problem is that deploying to a db that...more >>

Hi All, Sep19_2006 Sorry I am pretty new to sql server. What is the easiyest way to convert date in this format Sep19_2006 into sql server 2005 acceptable format? Thanks. ...more >>

I have to test the Following Stored procedure... /* starts here */ ALTER PROCEDURE dbo.InsertBuffer @QVID int, @xGene xml, @Type varchar(50), @isBindingIncomplete bit, @PID int, @WID int, @UserID int, @nRow int, @nColumn int, @SurfaceID int AS /* SET NOCOUNT ON *...more >>

Hi I poste dthis in another forum but got no help, I am trying to run a backup job and when I run the job manually in EM I get an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently not running and cannot be notified. In the event log I get these two below but the agent is...more >>

Hi all, i'd appreciate if anyone could tell me how to select the last filled row of a table before a given timestamp (date). E.g. if the timestamp has date 20061101 i need to select the row with the timestamp right before the given one. Eg if I the last filled row before 20061101 has timest...more >>

hi can i use a curssor in an update trigger ? the problem is to identify which field is updated in each row. can i just pas all the line to the curssor and check IF UPDATE(field name) and it will work ? ...more >>

Hi, Is it safe to shrink an offline database while the rest are concurrently used/have online connections and/or busy? Thanks! -- Regards, VZ...more >>

Hello World, We have used SQL Server 2000 (using the ODBC interface) with our software and it worked without problems. Now we've tried SQL Server 2005 and get the following error when SQLBulkOperations is called: ODBC ERROR -1: [0x1C22, 42000], Der Server 'SRV-SQL2005' wurde in 'sysserver...more >>

Hello I am starting to work with SQL-SERVER express and am looking at the range of MS development tools All I really need is the ability to create tables, relationships, views and a few Procedures in SQL. What are the main differences in working with "SQL server management studio expr...more >>

Hello; Where is the "Process Info" In SQL 2005 . It was under the "Enterprise Manager -> SQLServer -> Management -> Current Activity" in SQL 2000. Thanks. ...more >>

Hello, Excuse me if this has been asked an answered by another, but I couldn't find it Googling. Run the query (see below) and these results come back as anticipated: customerID, productName, orderCount 1 yyy 23 1 aaa 10 1 bbb 5 2 ccc 56 2 zzz 13 2 aaa 7 3 eee 11 3 fff 8 3 dd...more >>

I am designing a system where any number of users can log and record their own data into an SQL database. using relationships and queries I can make sure that each user only sees records that they have entered but is there a more secure way to prevent users seeing someone else's information...more >>

Hello, On the query below, SQL Server 2005's Database Engine Tuning reports "no recommendations". Yet on a 2 million plus row table it's taking 40 seconds (with no indexes). I'm presuming with the right index(es) performance will improve and since this table grows to +25 million rows before...more >>