Let me fitst say that I have found this forum to be VERY good and the members have an excelent knowledgebase.
I am very impressed and appreciate all of the help.
I have a table in my SQL database that I need to modify a little and which contains a lot of data.
My question is in wondering if it is possible to write a function that would loop through all of the rows in a particular column field and update the values?
What I mean is that I have a few columns (fields) in that table that hold numeric values like (1, 2, 3, 4, 5, .......) and I need to loop through all of the entries in that table to add 10000 to each one and store the results back into that same table.
Example is to replace 1 >> 10001, 2 >> 10002, 3 >> 10003, etc.....
Could some one please tall me how this can be done and what I need to do?
Hello, everyone!Does anyone know how I can pull additional field in a database whenthe max() of one field is pulled. For example:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetimeFROM table1GROUP BY foreign_id================================================== ===Here I am trying to pull the records that have the latest date foreach foreign_id. The result set above will pull foreign_id andmax_recordcreateddatetime but I needed to also have it display onemore field, current_status like this:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetime, current_statusFROM table1GROUP BY foreign_id================================================== ===The problem however is that SQL wants me to add this field to GROUP BYor use an aggregate function with it and I don't want any aggregatefunction processing - I just want current_status to show up there forme to see. How do I do this?Thank you for any input in advance!Roumen.
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block ALTER PROC csp_ICASTransaction_AskDoc @ClientID INT AS DECLARE @ClientID INT SET @ClientID = 1 DECLARE @cClientID INT DECLARE @cMonth VARCHAR(20) DECLARE @cOccurance INT DECLARE @Counter INT DECLARE @AskDoc CURSOR SET @AskDoc = Cursor FOR select @ClientID, Month_Year, SUM(AskDocs) FROM zzenrolled$ WHERE ICASClientID = @ClientID GROUP BY Month_Year OPEN @AskDoc FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance /* SELECT @@Cursor_Rows PRINT @cClientID PRINT @cMonth PRINT @cOccurance */ DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT) WHILE (@@FETCH_Status = 0) BEGIN SET @Counter = 0 INSERT INTO @MyTable VALUES (@cClientID, CASE @cMonth WHEN 'Jan-03' THEN '20030101' WHEN 'Feb-03' THEN '20030201' WHEN 'Mar-03' THEN '20030301' WHEN 'Apr-03' THEN '20030401' WHEN 'May-03' THEN '20030501' WHEN 'Jun-03' THEN '20030601' WHEN 'Jul-03' THEN '20030701' WHEN 'Aug-03' THEN '20030801' WHEN 'Sep-03' THEN '20030901' WHEN 'Oct-03' THEN '20031001' WHEN 'Nov-03' THEN '20031101' WHEN 'Dec-03' THEN '20031201' WHEN 'Jan-04' THEN '20040101' WHEN 'Feb-04' THEN '20040201' WHEN 'Mar-04' THEN '20040301' WHEN 'Apr-04' THEN '20040401' WHEN 'May-04' THEN '20040501' WHEN 'Jun-04' THEN '20040601' WHEN 'Jul-04' THEN '20040701' WHEN 'Aug-04' THEN '20040801' WHEN 'Sep-04' THEN '20040901' WHEN 'Oct-04' THEN '20041001' WHEN 'Nov-04' THEN '20041101' WHEN 'Dec-04' THEN '20041201' WHEN 'Jan-05' THEN '20050101' WHEN 'Feb-05' THEN '20050201' WHEN 'Mar-05' THEN '20050301' WHEN 'Apr-05' THEN '20050401' WHEN 'May-05' THEN '20050501' WHEN 'Jun-05' THEN '20050601' WHEN 'Jul-05' THEN '20050701' WHEN 'Aug-05' THEN '20050801' WHEN 'Sep-05' THEN '20050901' WHEN 'Oct-05' THEN '20051001' WHEN 'Nov-05' THEN '20051101' WHEN 'Dec-05' THEN '20051201' WHEN 'Jan-06' THEN '20060101' WHEN 'Feb-06' THEN '20060201' WHEN 'Mar-06' THEN '20060301' WHEN 'Apr-06' THEN '20060401' WHEN 'May-06' THEN '20060501' WHEN 'Jun-06' THEN '20060601' WHEN 'Jul-06' THEN '20060701' WHEN 'Aug-06' THEN '20060801' WHEN 'Sep-06' THEN '20060901' WHEN 'Oct-06' THEN '20061001' WHEN 'Nov-06' THEN '20061101' WHEN 'Dec-06' THEN '20061201' WHEN 'Jan-07' THEN '20070101' WHEN 'Feb-07' THEN '20070201' WHEN 'Mar-07' THEN '20070301' WHEN 'Apr-07' THEN '20070401' WHEN 'May-07' THEN '20070501' WHEN 'Jun-07' THEN '20070601' WHEN 'Jul-07' THEN '20070701' WHEN 'Aug-07' THEN '20070801' WHEN 'Sep-07' THEN '20070901' WHEN 'Oct-07' THEN '20071001' WHEN 'Nov-07' THEN '20071101' WHEN 'Dec-07' THEN '20071201' END ,'3' )
SET @Counter = @Counter + 1 IF @cOccurance > @Counter FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance END CLOSE @AskDoc --INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance]) SELECT * FROM @MyTable DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYEmy query looks likeselect ID, DateDue, dateReceived, dueday, month, ContactFYEfrom Reportwhere (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )user defined functionCREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime) RETURNS smalldatetime AS BEGIN DECLARE @ReturnString AS smalldatetime Declare @dueday1 as int if (@month =1)beginset @dueday1 = @dueday -7endelse if (@month =2)begin set @dueday1 = @dueday -14end else if (@month =3)begin set @dueday1 = @dueday -60endselect @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE) Return @ReturnStringENDI got a incorrect result when using this query and user defined function.the result that I got was out of between displaytime and datdue. any idea for this , Thanks.
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
Is there a way to get SQL Server to be based on GMT (Greenwich MeanTime)? If so, would there be no impact of the fallback time change on 28Oct01? We're planning to stop SQL Server for 1 hour during the time change period so the transaction and error logs do not have duplicate times or events and transactions out of sequence. Is this common practice? Thanks
My clients hosting company does not allow any direct interaction with SQL Server ie no Enterprise Manager/ Query Analyser access!
Are there any free web pages that i can download and then host on the clients server that allow me to create tables and stored procedures simply without writing a script for everything?
Hi, I'm rather new here, but I would like to add the question here since this project encompasses a lot of areas of ASP.NET (Visual Studio 2005). My project is about a site which is heavily based on Search Option. So basically, users will just have to fill in the forms, click search and it will search throughout the database. For example... The website is about a property listing. The database (based on SQL Server, can be created right through the Visual Studio 2005) consists of these attributes: ID, Property Name, Property Location, Property Cost. The user may search based on Name, Location and Cost. For the cost, there are two forms to fill: the lowest cost allowed and the highest cost allowed.All these are put on the Master Page (Located on the top). When the button Search is clicked, it will display the records that match the forms filled, in the main page located below the Master Page.If anyone can help me with this, I'll be very grateful. Thank you very much.
I have database created using MSSQL. Now I need to copy the database into a new server so the database can be used in a MSDE based server. With MSSQL I can use "Attach...." to attach the database to the SQL Server. How can I achieve the same thing with MSDE?
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt] FROM [dbo].[SF_Affiliate_Customer] C WHERE ( [afflt_intrnl_cust_ind] = 'N' AND [afflt_empl_ind] = 'N' AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA') )AND
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date
DECLARE @table TABLE ( Prop_Code INT ,Current_Rent INT ,Revised_Rent INT ,Rent_Review_Date varchar(10) ,Rent_Review_Time DATEtime)
I am about to rebuild all my indexes on a database that is very heavily fragmented. In looking at the report, seems that 80% or more tables are 90%+ fragmented.
My understanding is that fill value value is used for performance reasons. Our shiny new backend SAN is 100% SSD. If solid state can provide a sub-millisecond response, is fill factor still necessary at the cost of additional space being used used?
There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:
Step One: We create a Server Role called SQL_APPLICATIONS – for the application accounts We create a Server Role called SQL_DBA – for the DBA accounts and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two: I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three: I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
This local test DB was doing just fine. I fire up SQL Server again, and all of a sudden I can't access it (expend the database in Management Studio). So I detach and try to run this script which was run initially to re-attach it:
USE [master] GO CREATE DATABASE [ABS] ON ( FILENAME = N'C:DatabaseABS.mdf' ), ( FILENAME = N'C:DatabaseABS_log.ldf' ) FOR ATTACH GO if not exists (select name from master.sys.databases sd where name = N'CMS' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [CMS].dbo.sp_changedbowner @loginame=N'domain estuser', @map=false GO
and I get this error: SQL Server detected a logical consistency-based I/O error: torn page
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate order by case when pr.status=2 then 1 when pr.status=4 then 2 when pr.status=3 then 3 when pr.status=1 then 4 end ,pr.sdate )pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
I need to find all information about distributing SQL Server XE and applications using it . Is it legal ?? should it be used just for educational use?!!! could it be used to develop a commercial software !! what limitations are controlling this ??
We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (
I searched for a viable answer, work-around, or hotfix that would address this issue but found none.
The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.
The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.
Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.
Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?
I have an VB.NET application connected to a SQL Server Express. I want to let the application to run in either "Normal mode" or "Holiday mode" according to current weekday is normal day or state public holiday.
My approach is to find out all the public holidays in a year and enter them into a Holiday Table. Then some code in my application constantly check the current weekday against the one in the holiday table, if matches, the application goes into holiday mode.
This approach is not perfect as "State public holidays" are confirmed by the state government in the current year and the coming year. So state public holidays are unconfirmed for the third year afterward. The system is required by client to support public holiday in the next 10 years.
I wonder what is the best approach to this problem?
We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test.
However, when we get to a page that tries to access our Sql Sever Express database, we get the following error: An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Does anyone know how to overcome this problem? Any help appreciated. Claude.
I’m working with a team of programmers who are in the process of upgrading an older ASP application to ASP.NET 2.0 (VB). The existing application connects to the SQL Server Database using different Logins, depending on the User logged into the ASP Application.
We are hoping to do the same using ASP.NET 2.0, although we haven’t been able to find a simple solution. The most feasible solution we have found includes programmatically trapping all the Selecting etc events and changing the SqlConnection at run time. This allow for ease during Designing of the Web Pages, but implementation requires adding code to each web page (>100 pages).
We are hoping there may be a simple solution which can be implemented once for the application where the event can be trapped at the application level, and the SqlConnection set at this point. Or possibly another type of solution such as creating a new class etc that can be used?
Does anyone have a simple solution where the username / password for the Database Connection can be set at run time?
CREATE TABLE #Names ( ID INT IDENTITY(1,1), NAME VARCHAR(100) ) INSERT INTO #Names VALUES ('S-SQLXX') INSERT INTO #Names VALUES ('S-SQLXX.NA.SN.ORG') INSERT INTO #Names VALUES ('S-SQLYY') INSERT INTO #Names VALUES ('S-SQLYY.NA.SN.ORG') INSERT INTO #Names VALUES ('S-SQLCL-HR') INSERT INTO #Names VALUES ('S-SQLCL-MIS') SELECT * FROM #Names
--I want to filter out S-SQLXX.NA.SN.ORG because S-SQLXX.NA.SN.ORG is a duplicate of S-SQLXX eliminating .NA.SN.ORG from it.
--I want to filter out S-SQLYY.NA.SN.ORG because S-SQLYY.NA.SN.ORG is a duplicate of S-SQLYY eliminating .NA.SN.ORG from it.
--However I want to keep S-SQLCL-HR and S-SQLCL-MIS in my list of names as they do not have .NA.SN.ORG as a part of their name
--I want ONLY these returned IN the SELECT
SELECT * FROM #Names WHERE ID IN (1,3,5,6) DROP TABLE #Names
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1 Where Exists ( SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END) --,SearchNo --,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
RO1 BOOK RL1 PDF/ECOPY RO2 PAPER RL2 CONFERENCE RO5 JOURNAL RL11 OTHER
Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…
Something for example (I am not trying to write a condition: my question is how to write it),
if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.
Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…
See the attachment for better formatting...
--------------------------------------------------------------------------------------------- status location contact --------------------------------------------------------------------------------------------- ONLINE WEB BOB ON PRESS FOR PRINT S.R STREET, LONDON SMITH
What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.
Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.
Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.
