I have a table in which I have a parent_id and child_id field. I may then have a record
in the same table where the child_id of the first record is now the parent_id of the new
record with a new child_id. And that child_id is now the parent_id in a new
record with a new child_id, and so on, and so on. How do I query this table to get
all related parent and child_ids?
pls anybody help me with this.i need to make a query where i have to display all names of a categoryheirarchically.C1-->C2-->C3-->C4where C1 is the top level categoryit shud b displayed as C1/C2/C3/C4Also there can b any no of category levels.pls anybody help memanu
View 8 Replies View RelatedI have a table:
ID int, AreaID int, SaleAmount float, SaleDate smalldatetime
AreaID is an foreign key to a second table of heirarchical area data:
ID int, ParentId int, AreaName nvarchar.
The heirarchy varies in depth for different parts of the country - sometimes 3 levels deep, sometimes up to six.
My problem is this: how do a construct a sproc that will allow me to pass in any area id and then return one or more result sets with all the child data of that area id grouped (to allow a SUM() of the sales data and a MIN() of the dates) by EACH LEVEL of the heirarchy?
Sales data only exists in the bottom one or two levels.
I've tried looking at CTEs but can't seem to crack the problem. I got close with a WHILE loop, but that kept grouping the data at the same level.....
Thanks
John
Expected output from this data if, say, I passed in 1 (the National level)
'National', 47.81, 20-Feb-08
'Super Region A', 37.81, 21-Feb-08
'Region 1', 16.81, 21-Feb-08
'Region 2', 21.00, 22-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08
'Sub Region 3', 7.70, 23-Feb-08
'Sub Region 4', 12.30, 22-Feb-08
and so on. Note that it doesn't have to be one table - each level could come back as a separate table (in fact, that might be helpful).
If I passed in 3 I'd get
'Region 1', 16.81, 21-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08
(NB these results are just typed in, so forgive typos please, and the sums and min dates are not necissarily the same as from the sample
data below)
CREATE TABLE [dbo].[SalesData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[AreaID] [int] NULL,
[SalesDate] [smalldatetime] NULL ,
[SalesAmount] [float] NULL
)
CREATE TABLE [dbo].[SalesAreas](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL ,
[Name] [nvarchar](64) NOT NULL
)
DECLARE @ID int
DECLARE @ID2 int
DECLARE @ID3 int
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( 0, 'National') --1
SELECT @ID = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region A')--2
SELECT @ID2 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 1') --3
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 1') --4
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 2') --5
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 2') --6
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 3') --7
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 4') --8
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region B')--9
SELECT @ID2 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 3') --10
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 5') --11
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 6') --12
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 7') --13
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 4') --14
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 8') --15
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 9') --16
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 10') --17
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 11') --18
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-5, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-6, GETDATE()), 2.81)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-8, GETDATE()), 4.14)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-9, GETDATE()), 1.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-2, GETDATE()), 1.02)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-3, GETDATE()), 3.13)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 6.17)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-1, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-4, GETDATE()), 4.29)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-5, GETDATE()), 4.46)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-6, GETDATE()), 3.33)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-1, GETDATE()), 3.92)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (12, DATEADD(d,-7, GETDATE()), 5.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-5, GETDATE()), 6.16)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-3, GETDATE()), 3.34)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-2, GETDATE()), 2.61)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-3, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (16, DATEADD(d,-4, GETDATE()), 8.28)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (17, DATEADD(d,-5, GETDATE()), 2.44)
This may be a basic question, but defining anything other than a cursoris preffered.I have, as an example, 2 tables. One with customer data (addresses,phones, etc), the other is a listing of all 50 states (a cross referencefor short state alias to long state name, i.e. FL - Florida, etc...).I want to sort the out put by state long name, and show each customer inthe state ... BUT ...the output needs to be like so:FloridaABC,Inc Address1 City, State Zip, other InfoDummy Corp Address1 City, State Zip, other Info...GeorgiaXYZ, Inc Address1 City, State Zip, other Info...etc ...This is a basic heirarchical listing. Can this be done with a singleT-SQL statement or are cursors needed?Thanks in advance."Excellence is achieved through 1% inspiration and 99% perspiration." A.Einstein*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Hi ,
I am having 2 data store .
1. Oracle 10g
2 SQL server 2000
My requirement is that , i need to insert some data from sql server database table to oracle database using sql server query analyser or interface.
If there is any way ,plz let me know it
Thanks
Abraham
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Code:
SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate
I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist
I have a query:
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion
FROM dbo.tblGP_Practices AS gp INNER JOIN
[Code] ....
I wanted to add anohter column where it counts the results of the followign query:
SELECT dbo.tblPatient.NHSnumber, dbo.tblPatient.DateOfBirth, dbo.tblPatient.DateReferralReceived, dbo.tblGP_Practices.OrganisationCode
FROM dbo.tblPatient INNER JOIN
dbo.tblGP_PatientLink ON dbo.tblPatient.PatientID = dbo.tblGP_PatientLink.PatientID INNER JOIN
dbo.tblGP_Practices ON dbo.tblGP_PatientLink.GPPracticeID = dbo.tblGP_Practices.GPPracticeID LEFT OUTER JOIN
dbo.tblAppointments ON dbo.tblPatient.PatientID = dbo.tblAppointments.PatientID
WHERE (dbo.tblAppointments.PatientID IS NULL)
is this possible?
I 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.
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
My SQL 7.0 server is currently querying the SAM database on the PDC for Windows NT authentication. How can I force it to use the SAM database on the server(BDC) that I specify?
View 1 Replies View Relatedi need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View Relatedif 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$]
Hello
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
Hello,
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
I am trying to run a query from a Linked server "ABCD"
Set @SQLCMD = 'Select * from TableName"
Insert into @TempTableName
Execute (@SQLCMD) AT ABCD
I am getting below error while running the above statements. When I Remove the Insert into @TempTableName it is working fine.
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ABCD" was unable to begin a distributed transaction.
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Thanks
Sean
Hi 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
I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".
Isn't there a cleaner way of doing this?
Hi all,
I need your help.
I have 2 different databases. One of them is in the SQL Server 2000 and the other on is in the SQL Server 2005. I need to make a query out of these 2 databases.
Is there any way that I can do this?
Thanks,
Hi,
I have two servers S1,S2.
I am currently connect to S1 but I want to Query a table from S2 without creating a linked server.
Is it possible.Can someone please post some information on how this can be accomplished.
ETL, Lookup ,Stored procedure anything is ok. I am using SQL Server 2005.This is a very urgent requirement.
Please help!
Thanks,
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
I'm not sure this is the right forum for this thread, but hopefully the Admins can move it if it isnt.
Here is my situation.
I had a web server with a web page that referenced an Excel spreadsheet.
(a href="c:webservernamexyz.xls" target=main)
there is a query to retreive data in the ss from a Access Database.
I had to move the website from one server to another, which included moving the data from an Access Database to a SQL 2000 Database, which sits on a SQL Server.
There is a DSN created on the web server for the connection.
From the web server, I can open the Excel Sheet and it queries the database fine.
My issue is that now when someone hits the webpage from their computer, they get the spreadsheet, but since they dont have a DSN on thier machine, the get an error stating "[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified."
Is there a way to specify to use the DSN on the web server so I wouldnt have to create a DSN on every machine that would need to access it?
TIA
IV
Hey,I'm not sure how I do this, basically I want to do is:select * from table1where nameField and numberField not in (select nameField and numberField from table2)Can I do a not in on two fields like this?
View 2 Replies View RelatedI am having one table in MS-Access say “Table1?Table1 Structure as Follows : -
ColA
ColB
1
A
1
B
1
C
2
A
2
B
2
C
3
A
3
B
3
C
My MS-Access query : -
SELECT First(ColA), First(ColB)
FROM Table1
GROUP BY ColA;Result : -
1 A
2 A
3 A
I am using this same Table & Query and Same Result in the SQL server but I am getting following output (but its wrong) Output : -
1 A Here I want Following output in the SQL Server: -
1 A
2 A
3 A
Please help me on this issue
say I have table like this
zip dma
50111 511
50111 512
50111 513
50211 511
50211 514
50211 515
and so on..there are like 48000 entries on this table. Now there are occassions where there is only one zip code listed one time. How would I get all the zip codes that are at least more than 1 time in the table. Like this case it would be 50111, 50211. I wouldn't care if there is a zip code that exist only 1 time.
I have a bit of a query.
I have all of the system and user databases from a previous 6.5 installation (NOT Backup files. These are straight copies of the operational databases!)
Here's what i need to know:
Is it possible to start sql server 6.5 alongside SQL server 2k?
If so, is it possible to log into sql server 6.5 in single user mode and attach all of the previous databases to rescue all the data that they contain?
I hope that you can help because the information that these databases contain are vital to the operation of our helpdesk.
Just a note: I inherited these from my boss, just incase anyone asks "why didn't you just back them up?"
Thanks.