How To Scheduling A Query In Sql Server 2005
Apr 16, 2008I want to schedule a query that ll insert a record in a table of the database at every 02:00 am. How should i do that. i am uisng sql server 2005?
Muhammad Saifullah
ADVERTISEMENT
Scheduling A Query To Be Executed At Given Time In SQL Server 2005
Apr 4, 2007Hi friend!
I want to schedule a query or procedure running at given time regularly eg: at 12AM daily. Please tell me how to do that in sql server 2005.
We use SQL server 2005 developer edition.
Thanks in advance.
Scheduling Job In Sql Server 2005
Apr 10, 2007Hi,
I created a scheduled job in sql server 2005 which takes a daily backup of database.
My problem is this job does not run automatically.but when i execute it manually it runs on that instance.
do i need to activate any servrice to have scheduling of jobs running perfectly on my machine ?
Please guide me on this.
Thanks,
Prashant
Scheduling A Task In SQL Server 2005
Oct 10, 2007
I'm currently using Windows Task Scheduler to run a few jobs. We would like to start using the SQL Server Agent to handle this. The problem I am having is in specifing the 'working directory' of the program. The program is generating some files in a temp directory withing the root of the application. For some reason when I schedule in SQL it is generating these files in the WinSys32 directory.
I am using the CmdExec type in my job step.
I am sure I may have left out some info. please let me know if you need anything else.
thanks.
Job Scheduling For Packages In SQL Server 2005
Aug 31, 2006----------------------
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
----------------------
Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.
I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITYSYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"
I've also done the same on the Step properties under 'Logging'
In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio
The error coming up on the job history is as follow: "Executed as user: PC1234SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1USER123. The last step to run was step 1 (step1)."
By the way the package (.dtsx) runs fine in BI Visual Studio)
Thank you very much.
Scheduling The Legacy DTS 2000 Packages In Sql Server 2005?
Sep 12, 2007
Hi Experts
I am trying to schedule a legacy DTS 2000 package in 64 bit Sql Server 2005.
I cannot use the DTSRun tool on the Command prompt of the Sql 2005. What about dtexec ?Can i run Sql Server 2000 DTS packages from the Command Prompt.
Is DTS 2000 the only way to do that?
Any Advise is greatly appreciated
Vic.
Works Fine Inside BI Dev. Studio, But Fails When Scheduling It In SQL Server 2005.
Jul 13, 2005Hi!
View 12 Replies View RelatedDTS Scheduling In SQL 2005
Nov 21, 2006Hi all,
I am trying to create scheduled job for DTS. I have created new DTS in SQL 2005 but not able to create job out of it.
I tried creating Credential and then to create proxy with this:
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomainmyWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
but could not get the job to schedule it. when i try to search for the DTS package(TestRev) it could not find it:
Is there a way that i can just call TSQL statement to run that dam DTS because it is too confusing to create job for DTS.
Thanks
Scheduling 2005 Profiler
Jan 16, 2008I want profiler to run for 5 minutes whenever the system cpu exceeds/reaches 80%. Is this possible?
TIA
Scheduling Legacy DTS 2000 In Sql 2005
Sep 7, 2007Hi Guys
There are a few dts packages in our sql server 2000 database which we schedule daily for at night for business purposes.
how can i schedule the same DTS packages in Legacy SQL 2000 DTS when we migrate the Databases across to Sql 2005.
Can that be done or we need to migrate the DTS Package to SSIS
Vic
Scheduling SQL 2005 SSIS Packages
May 9, 2007I have created and run SSIS packages from my workstation that are successful. When I Build and then Deploy the same package to the server and schedule it, it fails. I am using SQL authentication connecting from a SQL 2000 database to a SQL 2005 database via OLE DB connections.
The following is the exported result of the package...
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by User JDEREP. The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:46:06.38 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:46:05 PM Finished: 3:46:06 PM Elapsed: 0.75 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:30:01.15 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:01 PM Elapsed: 0.734 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:20:01.55 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:20:00 PM Finished: 3:20:01 PM Elapsed: 0.703 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:18:01.53 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:18:00 PM Finished: 3:18:01 PM Elapsed: 0.766 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Any help would be greatly appreciated...
Jim
Additionally, when I run this from the command line, I get the following
DTSER_FAILURE (1)
Jim
SQL 2005 Std And Scheduling Backups To Include Full, Diff., And Trans. Logs
Feb 28, 2008Regarding backups.
The first available time to do a full backup is at 11:00pm which also applies to diff. backups.
How often does the trans. logs need a back up?
Sql Server 2000 - Scheduling A Job?
Jul 11, 2006I'm going to be creating a few triggers on tables to add new records to a seperate table (AlertData). Come 6pm or so I want a scheduled job to automaticaly run to make an HTTP wbe request to my SendAlerts.ASPX page. The page will simply make a database call for all users and the AlertData table contents, build what it needs and send out all the email alerts.
My question is is how do i create such a scheduled job under Sql Server 2000? It should run everyday at 6pm EST (even if the dbase server is restarted) and make an HTTP web request to my URL.
Thank you.
Scheduling In SQL Server 2000
May 3, 2006Hi all,I have a case :I have an employee SQL-View (not table) in my sql server 2000 database. This view actually connect to an oracle database server, because all our employee data is located on the oracle database server.This linked server soon proven to be the cause of poor application performance (response time is very slow). So we decide to put a local copy of employee table in our sql server 2000 database. We have successfully copy it to our database. But the most up-to-date employee data is still reside on oracle server. To deal with this problem we need to scheduling a sinchronization between sqlserver 2000 and oracle everyday at 01:00 o'clock midnight.I never get task like this before, how to make a scheduling in sql server 2000? my friend suggest a dts scheduling but unfortunately I haven't found any tutorial about this for beginnerthanks
View 1 Replies View RelatedScheduling MS Access From SQL Server 7
Aug 18, 2000I am trying to get SQL Server 7 to automatically open an Access 97 database to perform some reporting functions. I have the Access invocation within a BAT file which works fine when I invoke it manually from Explorer but if I try to get a SQL Server Job to invoke it, it just hangs.
Is there some reason why SQL is unable to open Access ?
(Incidentally, the Access MDB calls a stored transaction to create some temporary SQL tables as part of its processing)
Regards,
Shep
Scheduling Tasks In SQL Server 2000
Nov 9, 2006Hi all
I want to schedule a task that checks if a file(s) exists on d:Acc and then run the sql script shown below i.e. with the file(s) to load the content of the file(s) every day new files will be loaded. after the file(s) are loaded the file(s) are deleted.
I currently can achieve the above using Java and sql server and running the program as a scheduled task... (only reason for change would be that i wouldnt require java, JDBC etc to run the software it would be easy manage, migrate etc)
I wanted to find out if it can be done with sql server directly? (i.e. check if file exist and ased on that carryout the task or other wise just ignore) What do i need to look for? does sql server have a procedural language in it to be used for such thing? does sql server have an inbuilt task scheduler?
I am currently using the following script in a java program please note that i have used fixed values in this script for readabliity (of what i am doing)..
@bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AccInv..VW_AccInvoice
FROM ''d:Invbatch2001.txt''
WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)
SQL Server Agent Jobs And Scheduling
Sep 19, 2007I created an agent job, and it runs fine, but I want more control over the scheduling. I need to schedule jobs to run Sunday through
Thursday at 8:00pm, skipping Friday.
How can I use the agent job interface and customize the schedule this way? Will I have to resort to a Windows Service and control the
scheduling that way?
Scheduling A Job Containing A DTS Package On Sql Server 2000
Jul 20, 2005I've tried several different possibilities but don't have enough basicunderstanding of the ownerships and permissions that are involved soI'm looking for someone to explain what is required:I created a DTS package that includes a drop table, create table, anddata transformation task. Originally, my owner was local to the pcworkstation with administrator priveleges. When I executed thepackage on demand (manually, not scheduled), it runs just fine. Noerrors. When I schedule the package to run at a scheduled day/time,it fails with an access is denied message.I originally was using the sql admin user and password in theconnection but the view job history kept referencing "Executed asuser: sqlservernameSYSTEM...." and errored with an access is deniedmessage. Where did it get the SYSTEM user from since my connectionused the sql admin user in my DTS package? Do I need to determine howto get the sql admin user or should the SYSTEM user really have to runthis scheduled job? How do I get the sql admin user to show as the"executed as user"?I created another admin user to see if it had someone to do with thesql admin account but I got similar results. I tried creating thepackage as an NT domain user but got similar results.I'm sure it has something to do with permissions and ownerships but Ireally don't have a clear understanding of how it was intended towork.Any advise you can give is greatly appreciated. I've read alot ofpostings and I'm still not clear on the requirements.
View 2 Replies View RelatedScheduling SSIS Package In Sql Server
Apr 3, 2008Hi,
I have created the package in ssis and i want to schedule it local machine without using integration services. Any workaround for scheduling the Package?.
Specify me the steps to configure the package.
Thanks
Regards
Ramesh.R
Scheduling A Data Export From Server
Nov 4, 2015I have a sql server query that when run, produces 8 columns of data and some rows of data.My colleague has this in a stored procedure, and each week is running a manual crystal report, exporting it into a data only excel sheet, and emailing it across to the rest of the business.Is there a much easier, more automated way of doing this? What would be the best method of storing this query so it could be scheduled, a stored procedure, or something else? The query is below -
DECLARE
@startdate datetime,
@enddate datetime
SET @enddate = DATEADD(wk, DATEDIFF(wk, 0, getdate()), -1)
SET @startdate = DATEADD(wk, -1, DATEADD(wk, DATEDIFF(wk, 0,getdate()), -1))
[code]....
Scheduling Stored Procedures For MS SQL Server 2000
Mar 31, 2005Is there any way to make MS SQL Server 2000 to perform some stored procedures, say 3 times a day? If so, how? Thanks.
View 2 Replies View RelatedScheduling Jobs In Sql Server Express Edition
Dec 7, 2005mangesh writes "HI Everybody,
Please help me to solve this problem. I am not getting any solution to run jobs over sql server express. it does not have sql agent service.please suggest alertnative.
Mangesh"
DTS Import - Error Handling And Scheduling- SQL Server 2000
Mar 10, 2002Hi,
I would like to transfer data from an oracle database to SQL server database 2000. I am using DTS Export/ Import Wizard for this purpose.
My queries are:
1. How can I write the write the Start time, End time, data records that failed to a log file with minimal amount of coding. (I found that Active X scripts can be written although the coding is quite complex).
Can I get some kind of pointers to simple coding that can ensure I log time of run of DTS and errors if they occurred.
2. There is an error log written by DTS, but this is written only if an error occurs and the details of error are not written. I am looking to log successful transfers too. Can the DTS package be modified, with minimal code to achieve this objective?
3. I tried scheduling the DTS through the Edit window that SQL Server 2000 provides but am not able to change the time of schedule.
How do I schedule it?
Thanks,
Karthik
Scheduling SSIS Package From SQL Server Agent Job Fails.
Feb 21, 2008
HI,
I am having a strange error coming in SQL server 2005.
I had a DTS package developed in SQl server 2000. Then i had migrated that DTS package to SSIS package using Execute DTS 2000 package. The conversion is successfull.
Then when i manullt execute this package it runs successfully without giving any error.
But then wheh i schedule a SQL server agent job for the same above SSIS package it fails giving me some cryptographic error as follows:
Message
Executed as user: CATOS-CGDBTUW02SYSTEM. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-02-20 23:00:01.00 Code: 0x00000000 Source: Execute DTS 2000 Package T... The package execution fa... The step failed.
Not sure why is this happening.
Manually running the SSIS package from Visual studio does not gives error, but the same SSIS package when is been schedule from SQL server 2005's SQL server agent job it fails giving above error.
Please help me out how to rectify whith this.
Thanks.. in advance
Regards,
Ashok
Integration Services :: Executing BAT File On Remote Server And Scheduling It
Jun 24, 2015I have to execute a .bat file on a remote server (that is used to stop and start services of an appl). The remote server doesn't have SSIS,SSMS installed. I want to create a package on my desktop the uses Execute process task and execute the .bat file on the remote server and then schedule it using the SSMS.
View 14 Replies View RelatedRunning A Distributed Query Against A Loopback Linked Server In SQL Server 2005 Is Not Supported
Aug 27, 2007I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
Can Distributed Query Read Sql Server 2000 From Sql Server 2005?
Nov 29, 2007if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
SQL Server 2000 To SQL 2005 Linked Server - Query Problem
Jan 16, 2008Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
Error Query Data Through Linked Server , SQL Server 2005
May 27, 2008Hello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
SQL Server 2000 Query Slowing Down In SQL Server 2005
Aug 14, 2006Hi everybody,
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
Thank you
Steve Gadoury
Sql Server 2005 Query Help.
Jun 4, 2008Hi,
I'm having a problem with the below query. I'm trying to show the total amount of placements(sales) group by each month. I also want to show how much of the placements(sales) have been invoiced again groped by each month. The problem I'm having is some placements(sales) have more two invoices linked to them so the placementfee for that placement is being doubled.
How do I write the below code so that it only sums placementfee based on a distinct placementid?
is it possible to but something like this in the where clause -
where p.placementid = distinct/unique
Thanks in advance
Dave,
select
DATENAME(MONTH, p.createdon) AS theMonth,
sum(p.placementfee) as 'Amount Boarded',
count(distinct p.placementid) 'Number of Deals'
,SUM(i.netsum) as 'Amount Invoiced',
SUM(p.placementfee)- SUM(i.netsum)as 'Waiting to invoice'
FROM placements p
left outer join placementinvoices pp on pp.placementid = p.placementid
left join invoices i on i.invoiceid = pp.invoiceid
WHEREp.createdon >= '20080101'
AND p.createdon < '20150101'
GROUP BY DATENAME(MONTH, p.createdon),
DATEPART(MONTH, p.createdon)
ORDER BY DATEPART(MONTH, p.createdon)
Sql Server 2005 Query Questions
Apr 4, 2006I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
View 4 Replies View RelatedComplex Sql Server 2005 Query
Nov 8, 2007Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.
create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)
Here is a populated table.
I have left gaps for better visual checks for you.
IDSourceIDSourceCodeSecurityBpriceBpriceSizeApriceApriceSizeImportDate
11sourceASecA100.2299.1212007-11-07 16:24:31.297
22sourceWSecH95.789.432007-11-07 16:24:31.297
33SourceXSecS50.56176.4442007-11-07 16:24:31.297
44SourceQSecZ87.982007-11-07 16:24:31.297
55SourceJSecH100.299.1222007-11-07 16:24:31.297
66SourceKSecU2007-11-07 16:24:31.297
77SourceTSecA50.56387.112007-11-07 16:24:31.297
81sourceASecA100.2699.1222007-11-07 16:26:15.123
92sourceWSecH99.54489.432007-11-07 16:26:15.123
103SourceXSecS50.56219.332007-11-07 16:26:15.123
114SourceQSecZ16.9887.982007-11-07 16:26:15.123
125SourceJSecH100.2199.1222007-11-07 16:26:15.123
136SourceKSecU2007-11-07 16:26:15.123
147SourceTSecA50.56287.1112007-11-07 16:26:15.123
151sourceASecA100.2187.1112007-11-07 16:26:15.123
162sourceWSecH99.6689.4322007-11-07 16:26:15.123
173SourceXSecS50.56219.332007-11-07 16:26:15.123
184SourceQSecZ16.98387.9832007-11-07 16:26:15.123
195SourceJSecH100.2399.1232007-11-07 16:26:15.123
206SourceKSecU2007-11-07 16:26:15.123
217SourceTSecA101.32587.1132007-11-07 16:26:15.123
...
I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:
SecurityMax_BpriceBprice_SizeBprice_SourceCodeMin_ApriceAprice_SizeAprice_SourceCode
SecA101.325SourceT87.114SourceA, SourceT
SecH100.23SourceJ89.432SourceW
SecS50.562SourceX19.33SourceX
SecZ16.983SourceQ87.983SourceQ
What is the sql query to do this please?
This is what I have started with but it is not correct...
select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCode