SQLServerCentral / SQL Server 7,2000 / SQL Server Newbies

How to update just the year in date fields (non datetime data type)

nic79 — Fri, 18 May 2012 11:53:35 GMT

Hi Guys,I have to write a script to update ALL date fields in the database my company's software uses. This is mainly an exercise to help me learn SQL (I am encouraged to use all resources). Fortunately, most of the date fields are in datetime, but I am having a hard time figuring out what to do with the varchar/bigint/nvarchar ones such as 2004-06-23, 1276449969378, May 20 2, 06/14/2011. I have to update all fields one year ahead, but leave the rest of the date unchanged. Can anyone please point me in the right direction of where to start?Thanks :)

Generate sql script of a table schema in SQL 2000 through script

ssa2010 — Thu, 17 May 2012 04:23:33 GMT

I wrote some scripts for correcting my old sql 2000 db schema compared to new sql 2000 db. This is done through a loop operations. First check if any table not found in my old db, then running a table creation script for creating that table. If any system stored procedures or scripts availble for table creation ?. I want table scripts like when we take 'generate sql script' process.

SQL Server automatic startup

kampate — Wed, 21 Mar 2012 19:13:02 GMT

I'm having a problem starting SQL Server service. I'm using SQL Server 2008 R2. It's troublesome for me to go to services to start it up every time I start my pc. It just wont work even if I put it on automatic, after several restarts, it just return back to manual. I did configure the startup type to automatic, but why does it change back to manual after several times I restarted my pc. Any help on this ?

Track a stored procedure progress on front end.

shweta-210049 — Thu, 26 May 2005 08:33:00 GMT

hi all,

I have a VB dot net allplication which calls a stored procedure.This stored procedure is required to do a lot of computation ,inserts, updates etc for a large amount of data. This is taking a very long time. Is there a way i can intimate the front end as to the amount wrk completed. some sort of a progress bar.

Since the control shifts to the proc i am unable to update the progress bar as the execution proceeds.

Please help

thanks in advance

shweta

Calculate Totals for ID numbers that have more than one entry

emmettjarlath — Fri, 10 Feb 2012 09:18:18 GMT

Hi,I have a table with about 700 rows, 100 ID numbers that have more than one entry. I need to add the amount for each ID number, who have the same date, to get the total amount. For example:ID Dated Amount1 2012-02-03 00:00:00.000 100.001 2012-02-03 00:00:00.000 25.001 2012-02-03 00:00:00.000 125.002 2012-02-04 00:00:00.000 50.002 2012-02-04 00:00:00.000 15.003 2012-02-07 00:00:00.000 190.003 2012-02-07 00:00:00.000 210.003 2012-02-07 00:00:00.000 60.003 2012-02-07 00:00:00.000 40.00The result should look like this:ID Dated Amount1 2012-02-03 00:00:00.000 250.002 2012-02-04 00:00:00.000 65.003 2012-02-03 00:00:00.000 500.00Any suggestions would be a great help. Thank you in advance,emmettjarlath

stored procedure slower than query sql server 2000

ssa2010 — Fri, 02 Mar 2012 01:29:08 GMT

friends,When I running a Stored Procedure in sql 2000 query analyzer, it take 35 seconds. But when I run this script directly, it takes 1 second. How can I Improve this stored procedure speed ?

Disable email notifications in SQL Server Agent

kathyoshea — Wed, 21 Mar 2012 14:00:07 GMT

I'm trying to disable the email notification that I originally set up in the Job Properties of a SQL Server Agent job. I have unchecked the 'email' box, but still receive emails. Any thoughts?

Unable to connect to Database from sqlserver enterprise manger

pragyan.banerjee — Thu, 29 Mar 2012 04:54:50 GMT

Hello All,I have all the credentials handy but still I couldn't connect to a database from enterprise manager,its giving the reason :"Sql server not exist or access denied".My question is how I can check the port number of that particular server in enterprise manager?need your help.Many thanks in advance.

SQL Server 2005 Job email notification alerts

Claudia-343424 — Tue, 18 Mar 2008 10:52:07 GMT

Hi there, I was wondering if somebody could tell me how to configure the notification alerts on a job properties. Basically I want to receive an email when a job is completed or is failed.Thanks

Programmatically copy stored procedure to another db

stuthedog — Wed, 07 Mar 2012 14:28:04 GMT

I am developing an app that will provide a separate database for each subscriber. When a new database is needed a stored proc on the master db fires. It creates a new db and default tables. So far so good. Now I need to copy over several stored procs from the master db to the newly created db. I do not want to maintain scripts or use 3rd party tools, it needs to be dynamic.Right now I am grabbing the SP contents from sql_modules then attempting to exec it against the new db. Problem is I dont' know how to change the database that exec() fires against, the default db when this stored proc is run is the Master, I need it to be the target. I've tried changing the procedure declaration to CREATE PROCEDURE [MyNewDb].[dbo].[AwesomeSP] but sql complains'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.Any suggestions?MS SQL 2008R2

DBCC Transaction Logs - How to read/interpret

Klarence A. — Thu, 01 Mar 2012 19:11:12 GMT

Hi Guys,How to read/interpret the transaction logs displayed by DBCC command? can I convert the records into human readable format? If so, how?Thanks in advance!

SQL 2003 Help needed

debbie.golding76 — Thu, 01 Mar 2012 02:27:51 GMT

Hi all, I'm new to SQL, havent had any training at all and been sat in front of a PC and told to 'get on with it!' (In the nicest possible way of course)Anyway, my problem is, I have an import.sln (is this the right terminology?) that basically takes some .XML/Access Db's and imports the data. This has worked for with no problems for the last couple of months fine, but I've done a new download, created the new XML's/db's, copied them into the relevant area, clicked my green arrow and got the following error:Package Validation Error:Additional information:Error at Diagnostic Test[SSIS.Pipeline]: "component "Diagnostic Test MDB" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".Error at Diagnostic Test[SSIS.Pipeline]: One or more component failed validation.Error at Diagnostic Test: There were errors during task validation.(Microsoft.Data.TransformationServices.VsIntegration)can anyone help and explain it in simple terms for me please? I am a bit thick sometimes when it comes to things like this.Thanks

BCP, field terminator 0xBF with -w?

northyen — Mon, 27 Feb 2012 09:24:44 GMT

Hi, I'm desperately trying to get an inverse question mark out, as a seperator, but I need to define this in a bat file and that won't work. I've tried changing the codepage to no avail.So I know decimal value of inverse question mark is 191, which equals 0xbf in hex. But when I use this in the following, the output gets garbled-bcp "SELECT * FROM db.dbo.tbl" queryout OUTPUT.DAT -S server -U user -P passy -n -w -t0xbfWhen I analyse the file (hex view), I see that BF is not followed by 00, and that causes the problems, it should be BF 00 as that works.The thing is that I can get it to work with inverse question mark, but only when I typing the above command directly in the cmd window, bcp "SELECT * FROM db.dbo.tbl" queryout OUTPUT.DAT -S server -U user -P passy -n -w -t¿But ¿ doesn't work in a batch file.

Update the duplicate records in SQLserver

giribabugeda-633679 — Wed, 03 Jun 2009 04:27:59 GMT

Hi,i am loading data from text file to the db using a dts package.it involves follwing stages1)loading data to staging table from textfile2)matching the records in the staging table with the existing records and updating the staging table "consumerid" column with the consumerid in the Db3)updating the records in the oroginal table from staging table if consumerID has a value in the staging table after the matching process is done4)finally inserting all the records into the original table whose consumerids in the staging table are Nullthe table structure of the staging table is as follows.StagingId |ConsumerId |firstname |secondname| nationalIDno |Accountno | address 1 | |a | b | 123456 |12345089 |abcabc 2 | |a | b | 123456 |36780900 |abcabci have two original tables one for storing personal info and other for storing Account infoorignal tables structure should be as followsConsuemrId firstname secondname nationalIDno 1 a b 123456 ConsumerID AccountNo1 123450891 36780900from the above tables u can observer the same consumer has two different accounts.Now the problem is if i am inserting the record for the first time in the database which means i check for the record existance in the original table and the record does not exist which means the "consumerId" in the staging table has null values.and it is inserting the data as two different records and creating two consumerIds how do avoid this duplication problem.thanks in advance.

Access 2007 FE with SQL 2008 BE

dcsurfer67 — Mon, 06 Feb 2012 14:03:05 GMT

Hi all,Not sure if this is the right place to post this, but here goes.I'm not a DBA by any stretch of the imagination, but I've inherited an access 2007 DB at work. The access DB has an access FE linked to tables in the access BE which is stored on a network share. Yes, many issues with this setup, including the fact that now more than 3 people will be accessing the DB at the same time.Using the Microsoft SQL Server Migration Tool I've migrated the BE tables to SQL Server 2008. The interesting thing is that when I link the access front end to the tables now in SQL a second set of tables are created in the access front end. From the little I could find on the subject, it appears that the tables in the front end are used to update the tables in the SQL server and vise versa. This bloats the front end quite a bit though.I deleted the tables that were created in the front end and have been doing testing. So far it appears that any changes I make to data through forms on the front end are written to the tables on the back end. In short, it seems to work without the tables that were added, but I'm concerned that I may not be seeing something that isn't working.Does anyone know the purpose of those tables that were created in the front end and if it's, or will be, an issue?Thanks!

Error 512: Subquery returned more than 1 value....

Muneeb Ahmed — Thu, 24 Jun 2004 15:20:00 GMT

Hi everyone,Can anyone plz help me to solve this issue.In my SQL Server Enterprise Manager, when I right click and select the restore command, I get the following error message:Error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.I'm totally baffled what this means...Thanx...and I appreciate whatever help I get.Muneeb.

Change Excel file name when exporting with DTS Package

teddy.sincire — Fri, 15 Jan 2010 10:13:58 GMT

Hello,I need to export data from SQL Server 2000 to an Excel file daily. I'd like the file to be named "file YYYYMMDD.xls". The ActiveX script task below works the first time, but I get an error every time the job runs after that. The error is "The Microsoft Jet database engine could not find the object 'New_table$'. Make sure the object exists and that you spell its name and the path name correctly."Can anyone please help? Thanks.Function Main()mydate =now()sFilename = "[i]file path[/i]" & Right(Year(mydate), 4)If Month(mydate) < 10 Then sFilename = sFilename & "0" & _Month(mydate) Else sFilename = sFilename & Month(mydate)If Day(mydate) < 10 Then sFilename = sFilename & _"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)sFilename = DTSGlobalVariables("LogFilePath").Value & _sFilename & ".xls"Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")oConn.DataSource = sFilenameSet oConn = NothingMain = DTSTaskExecResult_SuccessEnd Function

Updating Table Approach

pawana.paul — Thu, 19 Jan 2012 20:34:16 GMT

All,I have 2 tables Provider_Status_Meta_Data with columns (Code, Display_Status, Description, ...., .....) and Provider_Address_Validation with columns(Code, Display_Status, .........). There is information stored in Provider_Status_Meta_Data Table. When Code entered in Provider_Address_Validation table that code column should compare with code column of Provider_Status_Meta_Data and Display_Status of Provider_Status_Meta_Data should be updated automitically in Provider_Address_Validation table.Your help is highly appreciated.Thanks

How to Eliminate SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS OFF from generated scripts

CartoonHead — Fri, 22 Feb 2008 06:43:38 GMT

When I do some setting ONCE (7 years ago) and never need to do it again, I tend to forget where it was that I made that setting... and with all my guesses proving wrong and not-always-useful documentation, it certainly makes me feel like a newbie.When you generate SQL scripts from within Enterprise Manager, the generated script surrounds each CREATE PROCEDURE cluster with:[code]SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS OFF GO[i](create procedure statements)[/i]SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO[/code]I know there's a database or Enterprise Manager setting to eliminate this from happening, but I just can't find it. I recall that it's doing this because I've got a mismatch between settings in two different places, but that's all I remember.Can somebody remind my (sadly and rapidly aging) brain how to have the scripts EXCLUDE these statements?

Update Script to select

Ayie — Wed, 06 Aug 2008 20:05:04 GMT

Hello everyone, any can help me to this script i want to enhance this script into fastest way, because if i run this query its to long to process, i dont know why, the total records without distinct is only 2000 records, anyone can help me please thanks[quote]update ipadmin.GL_TXN set defunct = 'Y' where gl_group_id in (select distinct gl_group_id from ipadmin.gl_txn with (nolock) where transacted_on >= convert(datetime, '03/07/2008', 103) and transacted_on < convert(datetime, '03/07/2008', 103) + 1 and belongs_to_hcare in (100) GROUP BY GL_GROUP_ID)[/quote]

Script or formula to calculate Fiscal Week.

k.kue.vang — Tue, 20 Dec 2011 13:04:15 GMT

Hello.I have been asked to create an excel spreadsheet as a template for tracking projects usiing fiscal weeks. I have never done anything with formulas or scripts in excel before ... unless you count add an subtract formulas in cells. I am supposed to pick an end date, and then write a script to start calculating fiscal weeks backward from the end date. For example, in 2012, if I pick an end date in FW 10, the formula or script is supposed to take that date and start calculated FW week backward from that, starting with FW 1 to the left of the end date, and adding 1 to each cell to the left until I get back to FW 1.

How to display the first and second Doctor to see a patient?

t_bolden — Tue, 06 Dec 2011 18:51:41 GMT

I have to create a table where there are lots of columns pulling date from one table and two views. To display the columns is no problem. The problem comes into play when I have to alias two columns to show a patient who received services from the first two distinct doctors (doctor_1 and doctor_2). Each doctor has their own ID. I’m not sure if I should use the “distinct top (2)”, “rank”, etc… Here is what it sort of looks like. Date, LastName, FirstName, Address, City, State, Zip, Hostital, Room, Doctor_1,Doctor_2Any help would be greatly appreciated.

Retrieving extra rows........

marge0513 — Fri, 02 Dec 2011 08:27:54 GMT

Hello everyone, I have this SQL that returns the correct amount of rows which should be 2:Select Distinct A.File_Name, A.File_Desc, A.file_location, A.location_date, A.downloaded_date, A.downloaded_id, A.file_size, A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded from SDT_LOG A Inner Join ACTIVITY_LOG BOn A.file_name = B.file_name and A.downloaded_date = B.date_loadedI need to add another field which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct. The A table's structure is along with sampledata for 1st record:(I'm sorry this is hard to read. I cannot separate theinformation neatly for the forum for some reason). [code="plain"] Name Type ----------------- ------------------- FILE_NAME VARCHAR2(50) STLMK.txt FILE_DESC VARCHAR2(50) NON-RESIDENT FILE_LOCATION VARCHAR2(50) L:\\NonResFiles YEAR NUMBER(4) 2008 LOCATION_DATE DATE 10/10/2007 DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM DOWNLOADED_ID VARCHAR2(50) Cindy FILE_SIZE CHAR(10) 16212 DAYS_TO_REQUEST NUMBER(3) 60[/code]The B table's structure is along with sampledata for 1st record: [code="plain"]Name Type ---------------- -------------FILE_NAME VARCHAR2(50) STLMK.txtACT_CODE CHAR(2) DACT_DATE DATE 10/10/2007 ACTIVITY_ID VARCHAR2(50) downloaded onDATE_LOADED DATE 09/04/2008 9:17:00 AM [/code]The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system. There should only be 2 rows, one with Cindy with an act_code of D and one with Jason with an act_code of S. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Also, I get the 2 extra rows when I add activity_id field to the select.We use Oracle 10. What am I doing wrong?Thanks in advance!!

Update script NEEDED... Help.

kipp — Mon, 16 Jun 2008 04:50:42 GMT

I have a BIG need to figure out an update script that will search through table [b]MODS[/b] and checks for END_DATES that have been changed at the second level (ie. 11393.021) and the change has not taken effect down through the "like" project below it (ie. 11393.021.01,11393.021.02,11393.021.03...). The script will check all Project# at the second level to see if this is the case and make the change...Can any one help me at all please?Thanks in Advance.*** ORIGINAL ***Project Start Date End Date11393 11393.021 01/01/07 05/01/0911393.021.01 01/01/07 12/31/0911393.021.02 01/01/07 12/31/0911393.021.03 01/01/07 12/31/0911393.021.04 01/01/07 12/31/0911555 11555.191 08/01/07 09/01/0911555.191.01 08/01/07 09/01/0911555.191.02 08/01/07 09/01/0911555.191.03 08/01/07 09/01/0911555.191.04 08/01/07 09/01/09 *** AFTER SCRIPT ***Project Start Date End Date11393 11393.021 01/01/07 05/01/0911393.021.01 01/01/07 05/01/0911393.021.02 01/01/07 05/01/0911393.021.03 01/01/07 05/01/0911393.021.04 01/01/07 05/01/0911555 11555.191 08/01/07 09/01/0911555.191.01 08/01/07 09/01/0911555.191.02 08/01/07 09/01/0911555.191.03 08/01/07 09/01/0911555.191.04 08/01/07 09/01/09

Complex View using repeating groups

jon.messer — Wed, 30 Nov 2011 05:38:44 GMT

Hi,We are struggling with the development of a data view which allows us to group data into batches of 5 yearsThe raw data is available in the following format - where commas are new columns.Id 1, Id 2, Row Description, Year Num, AmountHowever, we want to output it to Word merge table using the following view formatId 1, Id 2, Row Description 1, Year 1 amount, Year 2 amount, Year 3 amount, Year 4 amount, Year 5 amountId 1, Id 2, Row Description 2, Year 1 amount, Year 2 amount, Year 3 amount, Year 4 amount, Year 5 amountId 1, Id 2, Row Description 3, Year 1 amount, Year 2 amount, Year 3 amount, Year 4 amount, Year 5 amountId 1, Id 2, Row Description 1, Year 6 amount, Year 7 amount, Year 8 amount, Year 9 amount, Year 10 amountId 1, Id 2, Row Description 2, Year 6 amount, Year 7 amount, Year 8 amount, Year 9 amount, Year 10 amountId 1, Id 2, Row Description 3, Year 6 amount, Year 7 amount, Year 8 amount, Year 9 amount, Year 10 amountso forth for as many records as exists in groups of 5 years (upto a max of 25 years).Does anyone know if this is possible using a view or set of of views / stored procs?Thanks,Jon

need sequence in datetime

Tiya — Tue, 24 May 2011 13:34:56 GMT

I am creating a table and I want sequential date ..ist possible?create table daysinfo(COMM1 datetime)insert into daysinfo values('1-05-2011') where 1 is day,05 month.and i want next date like (dont want to use insert)1-05-20112-05-20113-05-2011ist possible if we want daily attendance system and need all dates ?...i thought about many approach but please suggest some easy one

Migrating/Converting SSIS Packages into Stored Procedures

Bangaaram — Wed, 12 Oct 2011 07:52:45 GMT

Hi everyone, I have seen many articles and posts related to migration of Stored Procedures into SSIS Pacakges. However, I am looking for [b]migration of DTS Packages into SQL Stored Procedures on SQL Server 2000[/b]. I have only SQL Server 2000 installed on my machine.Can somebody suggest me the best articles asap?Thanks in Advance

dynamcally expanding columns

light_wt — Mon, 24 Oct 2011 03:27:46 GMT

I have an etl process that imports a "flat file". say, it has 2 columns and it is importing fine.Is there a way to automatically change the import so that it will know a third column is introduced and import that new column? So, no one needs to click on the icon and add new column.Thanks.

get results from 2 tables?

mick burden — Sat, 01 Oct 2011 14:28:32 GMT

I have a table which holds names and phone numbers and another table that records when they rang, I'd like to be able to query table1 to find numbers for a certain person such as zoe, but in the same recordset return the last time those people rang me or just thier numbers even if they hadn't rung, is this possible?Table1 Table2Name Number Number date of callZoe Home 123456 876344 01/01/2011 15:23Zoe Work 342452 323345 01/02/2011 07:22 tony 876344 876344 01/02/2011 08:12Alice 323345 876344 01/02/2011 09:00

Blowfish Encryption Toolkit on SS 2008 R2

nineteen-red — Thu, 29 Sep 2011 05:57:59 GMT

Hi,I have previously used the blowfish encryption toolkit quite happily in applications using SQL Server 2005. Now I am trying to run the very same apps on a SS 2008 R2 and am getting errors. The errors generated by the xp_blowfishencrypt function are 17750 error code which I believe relates to the dlls.Has anyone succesfully used the toolkit with 2008 R2 or had experience of solving this problem when migrating to 2008 R2.Many Thanks

How do I drop temp tables in Store Procedure?

ss5523 — Tue, 27 Sep 2011 12:11:01 GMT

Here is the piece of code that is causing the issue:[quote] declare @VP_ATTUID varchar(20) declare @id int declare @rowNum int declare @maxrows int CREATE TABLE #TempRevenue_GM (ATTUID VARCHAR(10), TITLE VARCHAR(10), VP_ATTUID VARCHAR(10), ATTAINMENT DECIMAL (20,2), Rank INT) IF OBJECT_ID('tempdb..#SRRANKS_VP') IS NOT NULL DROP TABLE #SRRANKS_VPselect ATTUID, Title, VP_ATTUID, [POS REV ATTAIN], IDENTITY(int,1,1) as RANK into #SRRANKS_VP from dbo.tbl_CDE_Daily where 1=0 order by titleinsert into #SRRANKS_VP select ATTUID, Title, VP_ATTUID, [POS REV ATTAIN] from tbl_CDE_Daily where title = 'VP' order by title, [POS REV ATTAIN] DESC select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VPselect @maxRows = count(*) from #SRRANKS_VP set @rowNum = 0 -- this will until the last row is reached WHILE @rowNum < @maxRows BEGIN set @rowNum = @rowNum + 1 -- this is where you can now do something like Pass the VP_ATTUID to the ranking -- process, for now, we will just print the VP_ATTUID to the output screen print ('Rank this VP! ' + @VP_ATTUID) SELECT ATTUID, Title, VP_ATTUID ,[POS REV ATTAIN] as Attainment INTO #tempSR --drop table #tempSR FROM dbo.tbl_CDE_DAILY WHERE Title = 'GM' AND VP_ATTUID = @VP_ATTUID select ATTUID, Title, VP_ATTUID, ATTAINMENT, IDENTITY(int,1,1) as RANK into #SRRANKS from #tempSR where 1=0 --drop table #RANKS insert into #SRRANKS select ATTUID, Title, VP_ATTUID, ATTAINMENT from #tempSR order by attainment DESC Select ATTUID, Title, VP_ATTUID,t2.Attainment, z.ranking as RANK into #NETRevenue from (select min(t1.rank) as Ranking,t1.attainment from #SRranks t1 group by t1.attainment) z join #SRranks t2 on z.attainment = t2.attainment and t2.Attainment <> 0 ORDER BY VP_ATTUID, RANK --SELECT * FROM #TEMPSR ORDER BY ATTUID insert into #TempRevenue_GM SELECT * FROM #NETREVENUE ORDER BY RANK -- now we grab the next row making sure the ID of the next row -- is greater than previous row select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VP where RANK > @id IF OBJECT_ID('tempdb..#TEMPSR') IS NOT NULL DROP TABLE #TEMPSR IF OBJECT_ID('tempdb..#SRRANKS') IS NOT NULL DROP TABLE #SRRANKS IF OBJECT_ID('tempdb..#NETRevenue') IS NOT NULL DROP TABLE #NETRevenue END-- declare @VP_ATTUID varchar(20) -- declare @id int -- declare @rowNum int -- declare @maxrows int CREATE TABLE #TempRevenue_CM (ATTUID VARCHAR(10), TITLE VARCHAR(10), VP_ATTUID VARCHAR(10), ATTAINMENT DECIMAL (20,2), Rank INT) IF OBJECT_ID('tempdb..#SRRANKS_VP') IS NOT NULL DROP TABLE #SRRANKS_VPselect ATTUID, Title, VP_ATTUID, [POS REV ATTAIN], IDENTITY(int,1,1) as RANK into #SRRANKS_VP from dbo.tbl_CDE_Daily where 1=0 order by titleinsert into #SRRANKS_VP select ATTUID, Title, VP_ATTUID, [POS REV ATTAIN] from tbl_CDE_Daily where title = 'VP' order by title, [POS REV ATTAIN] DESC select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VPselect @maxRows = count(*) from #SRRANKS_VP set @rowNum = 0 -- this will until the last row is reached WHILE @rowNum < @maxRows BEGIN set @rowNum = @rowNum + 1 -- this is where you can now do something like Pass the VP_ATTUID to the ranking -- process, for now, we will just print the VP_ATTUID to the output screen print ('Rank this VP! ' + @VP_ATTUID) SELECT ATTUID, Title, VP_ATTUID ,[POS REV ATTAIN] as Attainment INTO #tempSR --drop table #tempSR FROM dbo.tbl_CDE_DAILY WHERE Title = 'CM' AND VP_ATTUID = @VP_ATTUID select ATTUID, Title, VP_ATTUID, ATTAINMENT, IDENTITY(int,1,1) as RANK into #SRRANKS from #tempSR where 1=0 --drop table #RANKS insert into #SRRANKS select ATTUID, Title, VP_ATTUID, ATTAINMENT from #tempSR order by attainment DESC Select ATTUID, Title, VP_ATTUID,t2.Attainment, z.ranking as RANK into #NETRevenue from (select min(t1.rank) as Ranking,t1.attainment from #SRranks t1 group by t1.attainment) z join #SRranks t2 on z.attainment = t2.attainment and t2.Attainment <> 0 ORDER BY VP_ATTUID, RANK --SELECT * FROM #TEMPSR ORDER BY ATTUID insert into #TempRevenue_CM SELECT * FROM #NETREVENUE ORDER BY RANK -- now we grab the next row making sure the ID of the next row -- is greater than previous row select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VP where RANK > @id IF OBJECT_ID('tempdb..#TEMPSR') IS NOT NULL DROP TABLE #TEMPSR IF OBJECT_ID('tempdb..#SRRANKS') IS NOT NULL DROP TABLE #SRRANKS IF OBJECT_ID('tempdb..#NETRevenue') IS NOT NULL DROP TABLE #NETRevenue ENDdrop table #TEMPSRdrop table #SRRANKSdrop table #NETRevenuedrop table #SRRANKS_VP[/quote]When I run this code I get the following error:[quote]Server: Msg 2714, Level 16, State 1, Line 53There is already an object named '#SRRANKS_VP' in the database.Server: Msg 2714, Level 16, State 1, Line 68There is already an object named '#tempSR' in the database.Server: Msg 2714, Level 16, State 1, Line 71There is already an object named '#SRRANKS' in the database.Server: Msg 2714, Level 16, State 1, Line 75There is already an object named '#NETRevenue' in the database.Server: Msg 170, Level 15, State 1, Line 77Line 77: Incorrect syntax near 'z'.[/quote]I put drop table in and I still get the error. Can anyone please help! :crying:

Performance and Audit Logout

Burt — Sun, 07 May 2006 18:46:00 GMT

I've been trying to diagnose some performance issues, I ran the SQL Profiler tool and upon review,I found that all the SQLs are executing within one second.However, I did find something strange. A event called Audit Logout is taking a very long time. I'm not aware what exactly this does, but it is taking up a lot of CPU time and accounting for large reads.

First of all what is it? And Is it safe to disable it?

Thanks

set implicit_transactions off permanently?

bburke-781975 — Tue, 17 Feb 2009 10:44:48 GMT

I'm a noob just trying to gather information on these 3 minute blocked/blocking issues that we've been having for weeks involving our production database and here's a small part of what I've gathered so far...I have a third party com object, which I don't have the source code for, that we use to update our production application database. The COM object performs well with one SPID from the webserver running the web application doing all the inserting and updating of the various tables. In looking at a Profiler trace when things are going well, a SQL:BatchStarting/BatchCompleted SET IMPLICIT_TRANSACTIONS OFF shows up right before the INSERT/UPDATE commands and things finish up nicely.When things go wrong, a SQL:BatchStarting/BatchCompleted SET IMPLICIT_TRANSACTIONS ON shows up right before the INSERT/UPDATE commands, multiple SPIDs from the same webserver get involved in handling the INSERT/UPDATE commands, I get an X lock on a key blocking an attempt at a S lock on the same key by the two competing SPIDs and things finish up with the web server (a guess on my part) aborting the SPID waiting for the S lock after 3 minutes (with EventSubClass "2 - Rollback", ObjectName "INSERT" and Error "2 - Abort") and then the SPID with the X lock on said key shows SQL:BatchStarting IF @@TRANCOUNT > 0 ROLLBACK TRAN (with with EventSubClass "2 - Rollback" and ObjectName "implicit_transaction") and ends up rolling back as well.Not knowing much yet about how the different transaction modes, connections and SPIDs work together, I was wondering if there was an overriding setting for transaction mode per connection or connecting host that I could utilize?

Problems w/Install of Service Pack 4

Thomas Johnson-219652 — Wed, 25 Jan 2006 12:22:00 GMT

I previously downloaded and attempted to instal MSSQL Service Pack4. It appears to go on its merry way with the upgrad until it reaches about tw dozen Read-Only modules. In each case I answer "OverWrite" until it tries to process Replsys.sql. I get the following message "Error Running script:Replsys.sql(1)" When I close this error box the whole install process shuts down without any error message about exceptions, logs, or where I should go to correct this problem. Also, it has a file "smssql2ksp4.pdf" which cannot be brought up in Adobe? Have I gotten hold of an invalid copy of this upgrade? An assistance would be appreciated. . .Thanks in advance Tom J.

Get the Best out of the Database using Optimal Indexes

gopi 56209 — Fri, 26 Aug 2011 05:36:28 GMT

This free webinar is presented by Pinal Dave a Microsoft Technology Evangelist (Database and BI) and a MVPFriday, August 26, 2011 - 7.00 P.M to 8.00 P.M ESTRegister here - http://bit.ly/qtXVodWebinar Highlights - Index Best Practices - Index Worst Pratices - Dynamic Management Views and Index - Identify Missing Indexes - Unused Indexes and Duplicate Index

Decimal data type

andrew.smith-613492 — Tue, 09 Aug 2011 15:11:10 GMT

All,I have a column configured as data type decimal, precision 10, scale 10.If I try to enter 1.0 I get the error "the value you entered is not consistent with the data type or length of the column."I've also tried different configurations of precision and scale but always get the same error.Can anyone offer any advice on what I'm doing wrong?Thanks

Replication Questions and Issues

jplum — Tue, 02 Aug 2011 11:40:58 GMT

I am somewhat of a new SQL 2000 DBA. Less than 6 months. I have a questing with regards to Replication.Is it possible to change Transactional Replication to Merge replication with out having to delete the Transactional and creating a new Merge replication? Thanks for your response.

Calling an Oracle Stored Procedure and Function

Brian Connolly — Thu, 11 Oct 2007 08:34:22 GMT

Hi,I'm trying to call an oracle stored procedure and function via linkedServer but I seem to be getting syntax errors from SQL server. The stored procedure passes two parameters and should get four back. This is the layout of the command I am using:Select * from openquery([linked_server_name],'{ Call oracle_pkg_name.oracle_stored_proc_name(20,25,{OUT_MSG_STATUS_CD,OUT_MSG_STATUS,OUT_ACK_MSG,OUT_ERROR_MSG}) }')The function takes two parameters as input, returns a numbers, and also has one output:select * from openquery([linked_server_name], 'select oracle_pkg_name.oracle_function_name(''CLR'', ''AAAM'',{OUT_ERROR_MSG})as proc_status from dual') Thanks,Brian

group by/left outer join problem

annie.alexander — Mon, 25 Jul 2011 12:49:06 GMT

I was wondering whether anyone could spot why my 'group by' function is "incorrect Syntax" from the following query I'm running. The only thing that is highlighted as incorrect syntax by SQL Server is the "GROUP" by SQL Server. If anyone has proposed solution, I would really appreciate it!SELECT 0.SignedData, accountID, COUNT(*) AS NumOrder,CASE WHEN COUNT(domain_name) <= 5 THEN '1-5'ELSE CASE WHEN COUNT(domain_name)<= 10 THEN '6-10'ELSE CASE WHEN COUNT(domain_name)<= 50 THEN '11-50'ELSE CASE WHEN COUNT(domain_name)<= 100 THEN '51-100'ELSE CASE WHEN COUNT(domain_name)<= 500 THEN '101-500'ELSE CASE WHEN COUNT(domain_name)<= 1000 THEN '501-1000' ELSE '1001 or more' END END END END END ENDFROM [ Sandbox].dbo.[2011.07.01_all]Left Outer Join [ Sandbox].[dbo].[factable]AS facON [SignedData]= SignedData;GROUP BY accountIDorder by NumOrder desc~Annie

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)

rino19ny — Mon, 18 Jul 2011 01:40:43 GMT

win2k3 server sp2sqlserver 2k sp3we used to have this maintenance plan to backup our databases and now it is not working. i made sure that the destination disk have enough space but i still get the error:Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.the maintenance plan (when viewed in the properties) have this inside:EXECUTE master.dbo.xp_sqlmaint N'-PlanID 524C3A14-C8A9-46C7-A231-C53D305825E5 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "F:\dbbackup" -CrBkSubDir -BkExt "BAK"'[\code]the plan was created from Maintenance -> Database Maintenance Plans using gui so i doubt a typo is causing the error.googling for the error turns up mostly saying to check the space which i've done. the database is 200GB and i have allocated a 1TB drive for the destination.how else to fix or know the cause of the problem?