I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
Hi,As I wrote my message the solution came to me, so I thought I wouldpost anyway for others to see in case it was useful:Here is some sample DDL for this question:CREATE TABLE Source (my_value INT NOT NULL )GOINSERT INTO Source VALUES (1)INSERT INTO Source VALUES (2)INSERT INTO Source VALUES (3)GOCREATE TABLE Destination (value_type VARCHAR(10) NOT NULL,value INT )GOI would like to fill the destination with a row for the COUNT, SUM,MIN, and MAX. My own problem is of course much more complex than this,but this is the basic stumbling block for me now. So, the rows that Iwould expect to see in Destination are:value_type value---------- -----COUNT 3SUM 6MIN 1MAX 3The solution that I came up with was to add a Value_Types table:CREATE TABLE Value_Types (value_type VARCHAR(10) NOT NULL )GOINSERT INTO Value_Types VALUES ('COUNT')INSERT INTO Value_Types VALUES ('SUM')INSERT INTO Value_Types VALUES ('MAX')INSERT INTO Value_Types VALUES ('MIN')GONow the SQL is pretty simple:SELECT V.value_type,CASE V.value_typeWHEN 'COUNT' THEN COUNT(*)WHEN 'SUM' THEN SUM(S.my_value)WHEN 'MAX' THEN MAX(S.my_value)WHEN 'MIN' THEN MIN(S.my_value)ENDFROM Source SINNER JOIN Value_Types V ON 1=1-Tom.P.S. - I know that I did not include primary or foreign keys in my DDL.I'll leave it as an excercise to the reader to figure those out. Ithink the code adequately explains the concept.
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND from fcs.dbo.WorkSet as ws left outer join fcs.dbo.WorkAssignment as wa on ws.WorkSetID = wa.WorkSetID left outer join fcs.dbo.MeterSessionInput as msi on wa.worksetkey = msi.worksetkey
I usually do this in VB, so I'm pretty sure my problem is related to c#. I'm trying to save the contents of a multiline textbox into a SQL server NTEXT field. When I do the insert, the SQL looks like (I'm using a stored procedure): pr_Update 8,1,'line1 line2' You can see that C# replaced the newline character with
When I look in the table using the management studio, I see line1 (then two boxes) then line2. After I do a select, the string returned is line1
How do I insert text from a multiline text box into sql server, and get it back with the newlines? Thanks! ---Selden
Apologies if this question is similar to one posted under "Click once deployment" but I got no replies and I REALLY need to know the answer to my problem!
I am using VB on VS 2005 Express and SQL Server 2005 Express.
My application is nearing the stage where I can start to use real data (from the end user) for testing purposes. Once I am as happy as I can be, I will install the application on the user's machine and he will use the application for a few weeks, with real data, before giving me a list of problems/suggested improvements etc.
I will then need to make the changes and re-install the application on his machine without losing all the changes he has made to the DB in those weeks.
Presumably, there must be a way of updating the executables without making changes to the DB on the client machine.
This is a very serious issue for me as the data will be around 2,000 records each of over 60 fields and the raw data will come from a variety of sources including paper so the data inputting will be a massive task that I do not want to repeat!
Also, if the application were ever to be sold, then other users would want to apply updates to the software whilst keeping their own data secure.
I have tried re-publishing and installing the application after setting the Publish Status of the DB files to "Exclude" but the newly installed application fails to connect to the old version of the DB.
I would very much appreciate any help in this matter as I hope I haven't just wasted several months of coding!
I have a problem that has been frustrating me for a long time, simply because I know this has to be somewhere but I cant seem to find where to toggle this setting.
I have a large number of tables that I regularly send to a remote server using the DTS Export Wizard in SQL Server Management Studio 2005.
Although I have toggled "Enable Identity Insert", as well as "Drop and Recreate Destination Tables", my tables upon completion are missing their primary key constraints, as well as its Identity attribute.
I have to go in manually to each table and set the column that is the PK, and the Identity attributes - all my stored procs break as a result of the missing constraint.
What setting do I need to flag during the DTS Export that will preserve the key values for me on completion?
I have a field Char(10) named bank, and another one Char(15) named namebank.
I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.
Using Management Studio, when I import data from one identical database to another, any identity specifications get dropped from the destination database.
(From columns which have them on the source database and which had them on the destination database.)
When our rep distribution services time-out (several times a day) some dba's just restart the service while others restart the rep job which starts the service. Are there any differences between these two methods? Does restarting the service inherit any changes made to the job's attributes?
Hi,usually, I'm not using MS servers, but I have a big problem with aAccess table.I should create a web application for a Historical Dipartment.They have create a populated a Access database using unicodecompression field (for ancient language).I would like to export this table into MySQL o Postgres, but it'simpossible because when I export this table in a .txt o cvs format theunicode charaters have been "destroyed" for memory allocation problems(cause Access use a compression tool for unicode fields).Also with professional tools for dump Access to another DBMS.I would to know if using a MS SQL server I can skip this problem causeboth MSQLserver both Access are Microsoft product.Thank you ;)J
We have SQL 2000 (SP2) , Our SQL server agent showing green light as started from EM , It is succussfully restarting from NT Services too . But When I open the properties of the jobs from EM its gives error message SQL DMO error " SQl Agent is starting , try later " . And in EM Next run Date & Time not available .
MDDB database is accessable & in Log file its recovered fully
Hi Guys, Im having trouble stopping a transaction once the result on one or more of my sql statements rolls back the transaction. Take a look at one my first 'IF' statement (below). If the parameter '@Return' is 'False' i want the transaction rolleback and all sql execution to stop. But my problem is the rest of the sql gets executed and i end up getting rubbish data. Im still new to transactions so i think im doing something really wrong. Please take a look at my sql and help me out in what ever way you think is best. thanks in advance. MattBEGIN TRY SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION; BEGIN DECLARE @Return BIT; SELECT @Return = (SELECT IsReturnable FROM INVENTORY WHERE InventoryID = 6) IF (@Return = 'False') ROLLBACK TRANSACTION; DECLARE @Already_In INT; SELECT @Already_In = (SELECT ReturnedBy FROM InventoryInUse WHERE (CheckOutNumber = 375)) IF (@Already_In IS NOT NULL) ROLLBACK TRANSACTION; DECLARE @Outstanding As INT; SELECT @Outstanding = (SELECT (QtyTaken - 40) AS Outstanding FROM InventoryInUse WHERE (CheckOutNumber = 375)); DECLARE @OutstandingReason As VARCHAR(200); DECLARE @Get_Person_ID INT; Select @Get_Person_ID = (SELECT PersonID From [User] WHERE Username = 'matthewsk'); IF (@Outstanding < 0) ROLLBACK TRANSACTION; ELSE IF (@Outstanding > 0)BEGIN IF (@OutstandingReason = '') SELECT @RollBack = @RollBack + 1; UPDATE InventoryInUse SET ReturnedBy = @Get_Person_ID, ReturnDate = GETDATE(), QtyReturned = 40, QtyOutstanding = @Outstanding, WriteOffReason = 'Testing This' WHERE (CheckOutNumber = 375); UPDATE Inventory SET TotalQty = TotalQty - @Outstanding, QuantityInUse = QuantityInUse - (40 + @Outstanding) WHERE InventoryID = 6; END;ELSE IF (@Outstanding = 0) BEGIN UPDATE InventoryInUse SET ReturnedBy = @Get_Person_ID, ReturnDate = GETDATE(), QtyReturned = 50 WHERE CheckOutNumber = 375; UPDATE Inventory SET QuantityInUse = QuantityInUse - 50 WHERE InventoryID = 6; END; COMMIT TRANSACTION; END;END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRANSACTION;
Thanks for your help in my date problem. It worked perfectly. I was away and could not answer back promptly.
yesterday, i was inserting data from a data file into a SQL table. 1 record had a name field as varchar. But it accidently, ended with a &. For example, "New Construction &". when I am inserting, its taking all records except this record that ends with a &. Is there any way to include this one Or should I tell my client that this is an error. If its an error, what is the expalnation(Like string char does not end with a & and & is a ------). Thanks others in the forum.
I am sorry. "New Construction &". Its not a column name. Its just a record. The table has a column called Name and it accidently, got a record that has "New Construction &" for the Name column.
Its not inserting that record with "New Construction &" in the Name field. Other records are fine.
I am attempting to learn Service Broker from Bob Beauchemin's book "A Developer's Guide to SQL Server" - Chapter 11. I'm finding it to be very good but I'm confused over the concept of closing a conversation. Could someone answer the following questions for me?
When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not) Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply? Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?
Alright here's my situation. I have two servers both running Eval copies of SQL Server 2005 Enterprise. This is only because I had the promise that I would be getting a shiney new server with a fully licensed copy of the server soon so I wasn't worried when I setup these two programs to use these. Now they are dragging their feet to approve the server and software so I'm scared. First question - What will happen when the eval period runs out? Second question - Can I tell what the date was when I installed the server?
These both will help me push management to approve atleast the purchase of an SQL license if I can't get the server.
I'm using SQL SERVER 2000 and in the process of establishing recoveryguide.Tried to rebuild master using rebuildm.exe but the "Configuring Server"never ending. Then I tried to copy original databases from SQLServerInstallation CD to hard disk as the "Browse for folder", but gave thesame problem. Need advice what need to be done.
I have a process that seems to leave many orphaned sessions over a period of time. The software is by a 3rd party vendor and they can't seem to fix it. It is safe to end the orphaned sessions and I do that on a regular basis. My questions is: What is the best way to do this via T-SQL?
I can select the orphaned sessions by using a simple query on the sys.dm_exec_sessions table. How do I then run a kill command for each session_id from that query?
What i have tried has'nt even been close to a solution. UPDATE dbo.t_Work_Hours SET WeekEnding = DATETIME ( ??? , StartTime) . I have no idea where to go!
I've read Remus' article on Fire and Forget tactics when it comes to ending conversations, and I have to admit I am guilty of sending a message to a service and immediately ending the conversation. I've set up a stored procedure to end the conversation on the initiator queue, but I'm guessing it's never being fired because I am not ending the conversation in the target activated stored proc, which is a CLR stored proc.
Can you tell me how to end the conversation from a CLR stored proc?
Now that I think of it none of the code I use in my TSQL activated stored procs to handle different message types and error-checking is done in the CLR stored proc.
I have a job in SQL Server agent with 199 SSIS steps and growing. In the job log only 99 steps are shown, which is a problem if a step higher than 99 fails to run. I need the steps to run in sequence and preferable would like to split the job into several jobs each containing no more than 99 steps. The only thing I don't know is if it is possible to have the next job in sequence start only when the previous job has finished.
Does anyone know if this is possible, and if so how it is done?
Hi, I have a simple web application which calls a stored procedure. The stored procedure operates as a transaction and runs for several minutes. I've created a partial class to set the SQLcommand timeout property to avoid any timeouts, which works fine. Unfortunately though, when the application is run in the production environment, it ends in an error after a certain amount of time (maybe a couple of minutes - not exactly sure), which seems to be the same each run. It doesn't appear to end the stored procedure though, which results in locking the tables. It runs fine in the development environment, and it doesn't appear as though any error information is provided when the application crashes. I'm assuming that the ASP.NET application is timing out for some reason, but the stored procedure itself is fine. I can run it directly from SQL server without any dramas. In the Virtual Directory configuration within IIS, I have the script timeout period set to 1200 seconds. The Default Web-Site timeout property is set to 120 seconds, but I'm assuming that this is only for internet connection timeout, not database transaction timeouts. Any information as to what may be causing this is appreciated. Thanks
Morning Guys, I have a database where I need to kill a remote connection. If I was manually logged into the box i would you a netstat command to stop the service. I would like to end a process from another machine before starting a restore process
I need to write VS2005 C# code using SQL OLE DB to access SQL Server 2005. I have no choice in that matter. I can create a database user with a password like COMPANY", which is a string of uppercase characters ending in an unmatched double-quote mark.
Using Microsoft SQL Server Server Management Studio I login using Windows Authentication, create an account with the password, COMPANY", check the "Enforce password policy", click "OK", and then exit.
Setup
I launch Microsoft SQL Server Server Management Studio again, select "SQL Server Authentication", type in the account name and the COMPANY" password, click the Connect button, and I'm in.
Problem
Now, I need to connect programmatically and run a stored procedure. The password is stored in clear text in hte Registry€”not my choice, it's a legacy application, and changing that is not an option open to me. (We have probably all seen company safes where the combination is scribbled on the wall in case you forget it!)
If I set the connection string in the registry to COMPANY", I get an error like this:
Server Error in '/' Application.
Response is not available in this context. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: Response is not available in this context.
Source Error:
Line 60: catch (System.Exception e) Line 61: { Line 62: Response.Write(e.Message.ToString()); Line 63: } Line 64: Source File: C:sourceProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs Line: 62
Stack Trace:
[HttpException (0x80004005): Response is not available in this context.] System.Web.UI.Page.get_Response() +2077605 SiteIQWeb.CommonCode.Common..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs:62 SiteIQWeb.MasterPage..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebMasterPage.master.cs:21 ASP.masterpage_master..ctor() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files oot 255b545c8a400c7App_Web_hfj8popy.0.cs:0 __ASP.FastObjectFactory_app_web_hfj8popy.Create_ASP_masterpage_master() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files oot 255b545c8a400c7App_Web_hfj8popy.3.cs:0 System.Web.Compilation.BuildResultCompiledType.CreateInstance() +49 System.Web.UI.MasterPage.CreateMaster(TemplateControl owner, HttpContext context, VirtualPath masterPageFile, IDictionary contentTemplateCollection) +250 System.Web.UI.Page.get_Master() +48 System.Web.UI.Page.ApplyMasterPage() +18 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +685
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
The closes to success I have been able to come is to set the password to "COMPANYNAME""" or 'COMPANYNAME"', which results in a System.Data.OleDb.OleDbConection Exception with a HResult of 0x80040e4d, and the message Login failed for user 'username'.
Questions
Can the password be formatted differently in the registry, or somehow processed after retrieving it, so that SQL Server 2005 will accept it?
Is this a bug in .NET Framework or SQL OleDb?
Is this simply a case of "Is it hurts, don't do it"?
Thanks in advance. As with all postings, my job, promotion, product success, company future, or some combination thereof is on the line.